The INDEX function

Returns the value at a given row and column position in a range. Paired with MATCH it becomes a lookup in any direction.

How to use INDEX

INDEX picks a value out of a range by its row and column number, like reading a square off a chess board once you know the coordinates. =INDEX(A2:B4,3,1) means: go into this block, take the third row and the first column. If the range has only one column, the row number is enough.

On its own, INDEX can pull the fifth item from a list or one cell from a table by position. Its strength shows up with MATCH, which works out the row number while INDEX fetches the value: =INDEX(C2:C20,MATCH(F2,A2:A20,0)) finds the ID in column A and returns the sales from column C. Because INDEX points straight at the column you want, it can look left, and it survives an inserted column in the table.

For a lookup in both directions, use MATCH twice, once for the row and once for the column: =INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0)). A row or column number of 0 returns the whole row or column, which is handy inside SUM. Remember that the number counts from the first cell of the range, not from row 1 of the sheet. If you only need a single match, XLOOKUP does the same in one function.

Syntax

INDEX(array, row_num, [column_num])
array
The range to pick from.
row_num
The row position within the range, counting from 1.
column_num (optional)
The column position, for ranges more than one column wide.

Examples

D1=INDEX(B2:B4,2)
ABCD
1FruitPrice6
2Apple4
3Pear6
4Plum5

In plain words: Return the second value in the prices in B2:B4.

D1=INDEX(A2:B4,3,1)
ABCD
1FruitPricePlum
2Apple4
3Pear6
4Plum5

Common INDEX mistakes

  • INDEX returns #REF!

    The row or column number is larger than the range. =INDEX(C2:C20,25) fails because the range has only 19 rows. If the number comes from MATCH, check that MATCH searches a range of the same height.

  • INDEX returns the value from the row below the one I expected

    The number counts from the first cell of the range. =INDEX(C2:C20,10) returns the value in C11, not C10. To get row 10 of the sheet, start the range in row 1: =INDEX(C1:C20,10).

  • INDEX and MATCH return #N/A

    The #N/A comes from MATCH, which found nothing. Put the MATCH part alone in an empty cell and see what it returns. The usual causes are a missing 0 at the end, extra spaces and numbers stored as text.

  • INDEX shows 0 instead of an empty cell

    When INDEX points at an empty cell, it returns 0. Test for blank first: =IF(INDEX(C2:C20,MATCH(F2,A2:A20,0))="","",INDEX(C2:C20,MATCH(F2,A2:A20,0))).

Practice INDEX

Read the guide

Questions

Why use INDEX and MATCH instead of VLOOKUP?

INDEX and MATCH can look left and keep working when you insert a column in the table. In newer Excel, XLOOKUP does the same with one function.

How do I do a two-way lookup with INDEX and MATCH?

Use MATCH twice, one for the row and one for the column: =INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0)). G1 holds the row label and G2 the column header.

How do I use INDEX and MATCH with multiple criteria?

Match on the product of the tests, =INDEX(C2:C6,MATCH(1,(A2:A6=F1)*(B2:B6=F2),0)). Excel 365 handles this directly. In Excel 2019 and older, confirm it with Ctrl+Shift+Enter, and in Google Sheets wrap it in ARRAYFORMULA.

What does INDEX do with 0 as the row or column number?

It returns the whole column or row. =SUM(INDEX(B2:E13,0,2)) adds up the second column of the range.

Related functions