INDEX in Google Sheets
Returns the value at a given row and column position in a range. Paired with MATCH it becomes a lookup in any direction.
In Google Sheets
INDEX takes the same arguments in Google Sheets, and row or column 0 returns the whole column or row.
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
=INDEX(1B2:B4,22)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 6 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Return 2the second value in 1the prices in B2:B4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | Plum | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
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))).