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.
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(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.
D1=INDEX(A2:B4,3,1)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | Plum | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |