Lookup function
Excel INDEX function
INDEX returns the value at a given row and column position inside a range. On its own it is like saying "give me row 3, column 2". Combined with MATCH, it becomes a lookup that works in any direction.
=INDEX(array, row_num, [column_num])Arguments
array- The range to pick from.
row_num- Which row of the range to return, counting from 1.
column_num- Which column to return. Optional for a single row or column.
INDEX examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Size | Small | Medium | Large |
| 2 | Latte | 3.75 | 4.25 | 4.75 |
| 3 | Mocha | 4.1 | 4.6 | 5.1 |
| 4 | Cold brew | 3.5 | 4 | 4.5 |
| 5 | Chai | 3.6 | 4.1 | 4.6 |
Result: Cold brew
Returns the 3rd item in the drink list: "Cold brew".
Result: 5.1
Row 2, column 3 of the price grid: a Large Mocha costs 5.1.
Result: 4.1
Two MATCHes find the row and column, so INDEX returns a Medium Chai: 4.1.
Result: #REF!
There is no 5th row in the range, so INDEX returns #REF!.
How to use INDEX
- Type =INDEX( and select the range to pick from.
- Add the row number.
- For a grid, add the column number too.
- Replace hard-coded numbers with MATCH to make it a lookup.
Common INDEX mistakes
Row number counts from the sheet
row_num counts from the top of the range, not row 1 of the sheet. In B2:D5, row 1 is sheet row 2.
Position outside the range
A row or column number bigger than the range gives #REF!. Check the size of your range.
Ranges that do not line up
With INDEX/MATCH, the MATCH range must start on the same row as the INDEX range or results are shifted.
INDEX FAQ
Why use INDEX MATCH instead of VLOOKUP?
INDEX/MATCH can return from any column, including ones to the left, and does not break when you insert columns.
How do I do a two-way lookup in Excel?
Use INDEX with two MATCHes: =INDEX(B2:D5,MATCH(row_item,A2:A5,0),MATCH(col_item,B1:D1,0)).
Can INDEX return a whole row or column?
Yes. Use 0 for the other argument: =INDEX(B2:D5,0,2) returns the entire second column, which you can wrap in SUM.
Practice INDEX in these lessons
Guides that use INDEX
- How to use INDEX MATCH in Excel
- XLOOKUP with multiple criteria
- VLOOKUP vs XLOOKUP
- How to return multiple matches in Excel
- #REF! error in Excel
- Excel formulas cheat sheet: the 25 everyone should know
Related functions
Updated