Excel functions / Lookup & reference
INDEX
Returns the value at a given position in a range.
Syntax
=INDEX(array, row_num, [column_num])Arguments
array | The range to pick from. |
|---|---|
row_num | The position to return, counting from 1. |
column_num | Optional. The column, when the range has more than one. |
How it works
INDEX answers the question "what is the 3rd item in this list?" On its own it is simple. Its real power comes when another function, usually MATCH, works out the position for it.
With a two-dimensional range you give both a row and a column, and INDEX returns the cell where they cross.
Example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Stock | ||
| 2 | A10 | Lamp | $25.00 | 12 | Chair | |
| 3 | B20 | Desk | $140.00 | 3 | ||
| 4 | C30 | Chair | $85.00 | 7 | ||
| 5 | D40 | Shelf | $60.00 | 0 | ||
| 6 |
Result: Chair. Returns the 3rd product in the list.
Tips
- Positions count from the top of the range you give, not from row 1 of the sheet.
Practise INDEX
Try it yourself in a real spreadsheet, with instant feedback on every answer.