The guide · INDEX + MATCH
MATCH finds the position
=MATCH("NBO", C2:C8, 0) returns 4, because NBO is the 4th cell of C2:C8. It's a position inside the range, not a row number on the sheet. The 0 asks for an exact match.
INDEX returns what's at a position
=INDEX(A2:A8, 4) returns the 4th cell of A2:A8, which is Nairobi.
Together
Put MATCH inside INDEX and the position flows from one to the other: =INDEX(A2:A8, MATCH(E2, C2:C8, 0)). Because the two ranges are separate, the column you return can be to the left of the one you search, which VLOOKUP can't do.
Both ranges must start on the same row
MATCH counts from the top of its range and INDEX counts from the top of its own. If MATCH uses C2:C8 and INDEX uses A1:A7, position 4 means row 5 for one and row 4 for the other, and every answer is the city from the row above. There's no error, so check one result by hand.
Don't forget the 0
Without the third argument, MATCH assumes the list is sorted and looks for the closest smaller value. On an unsorted list of codes that can return a wrong position without an error. Always write 0 for exact lookups.
INDEX MATCH, VLOOKUP or XLOOKUP?
XLOOKUP does the same job in one function in Excel 365 and 2021 or later. INDEX MATCH works everywhere, including older files and interview tests that ask for it by name. VLOOKUP is fine when the answer is to the right of the lookup column.
Common mistakes
- Ranges that start on different rows. The answer is off by one row.
- MATCH without 0. Fine on sorted numbers, unreliable on anything else.
- Swapping the ranges. INDEX gets the column to return; MATCH gets the column to search.
FAQ
- Why use INDEX MATCH instead of VLOOKUP?
- INDEX MATCH can return a column to the left of the one you search, doesn't break when columns are inserted, and works in every version of Excel.
- What does the 0 in MATCH mean?
- It asks for an exact match. Without it, MATCH assumes the list is sorted in ascending order and can return the wrong position.
- Why does my INDEX MATCH return the value from the wrong row?
- The INDEX range and the MATCH range start on different rows. Make both start on the same row, for example A2:A8 and C2:C8.
- Is INDEX MATCH better than XLOOKUP?
- XLOOKUP is simpler if everyone using the file has Excel 365 or 2021 or later. INDEX MATCH is the safer choice for files shared with older versions.