The INDEX function
INDEX returns the value of a range at the position you give it: the 5th row, the 3rd column. Alone, it is of little use; with MATCH, which finds the position, it makes the best lookup there is.
Syntax
=INDEX(array, row_num, [column_num])
In a French Excel: =INDEX(matrice; no_lig; [no_col]).
Arguments
- array: the range.
- row_num: the row number within the range, 1 being the first.
- column_num: the column number, optional for a single-column range.
An example
=INDEX(Customers!$C:$C,MATCH(A2,Customers!$A:$A,0))
MATCH finds which row the code in A2 is on in column A; INDEX returns column C on that row. It is a VLOOKUP that can look to the left and depends on no column number.
Pitfalls
- A position outside the range returns #REF!.
- Forgetting MATCH's 0: the lookup becomes approximate.
Going further
- MATCH, its companion.
- XLOOKUP, which does the same in one function.
- Find the right formula: the INDEX and MATCH version, written for you.
Frequently asked questions
- Why INDEX MATCH rather than VLOOKUP?
- It searches any column, returns any other, and does not break when a column is inserted.