INDEX and MATCH work as a pair. Together they do everything VLOOKUP does and more, and they work in every version of Excel.
What each function does
- MATCH finds the position of a value in a list.
=MATCH("Priya", A2:A100, 0)returns 5 if Priya is the 5th name. - INDEX returns the value at a given position.
=INDEX(C2:C100, 5)returns the 5th item in column C.
Putting them together
=INDEX(C2:C100, MATCH(F2, A2:A100, 0))
MATCH finds the row where F2 appears in column A. INDEX then returns the value from that same row in column C. The 0 means exact match.
Why people prefer it to VLOOKUP
- It can look to the left, because the lookup column and return column are separate.
- Inserting columns does not break it.
- It can be faster on very large sheets.
Two-way lookup
You can use MATCH twice to find a value by row and column name, for example sales for a product in a given month:
=INDEX(B2:M50, MATCH(P1, A2:A50, 0), MATCH(Q1, B1:M1, 0))
The first MATCH picks the row (product) and the second picks the column (month).
Common mistake
Make sure the INDEX range and the MATCH range have the same number of rows, and start at the same row. If they do not line up, you will get the wrong answer without any error.