Lookup
INDEX
Definition
INDEX returns the value found at a given row, and optionally column, position inside a range.
Syntax
=INDEX(array, row_num, [column_num])
Arguments
| Argument | Meaning |
|---|---|
array |
The range to read from, such as B2:B5. |
row_num |
The position of the row inside the range, counting its first row as 1. |
column_num |
Optional. The position of the column inside the range; needed when the range has several columns. |
Worked example
A weather log lists monthly rainfall in B2:B5, and you want the third entry.
| A: Month | B: Rain (mm) |
|---|---|
| Mar | 57 |
| Apr | 66 |
| May | 38 |
| Jun | 24 |
Formula: =INDEX(B2:B5,3)
Result: 38. The third cell inside B2:B5 is B4, which holds 38.
Practice
Range D2:E4 holds tea names in column D and brew times in column E: Sencha 2, Oolong 5, and Rooibos 7. Write a formula that returns the brew time in the third row.
- In F2, write an INDEX formula that returns row 3, column 2 of D2:E4.
- F2 should show the brew time in row 3, column 2 of D2:E4.
Related functions
INDEX fetches a value once you know its position, MATCH finds that position, and VLOOKUP combines both steps for simple tables.
Common mistake
Using the sheet row number instead of the position inside the range: with B2:B5, asking for row 4 returns the third item, and asking for row 5 returns #REF!.