INDEX MATCH in Excel: How to Use It with Examples
INDEX and MATCH are two separate Excel formulas that become far more powerful together. Used as a pair, they do the same job as VLOOKUP — find a value and return matching information — but without VLOOKUP’s two biggest limits: they can look in any direction, and inserting a column never breaks them. If you work with spreadsheets that other people edit, this is the lookup combination worth learning.
What each formula does on its own
INDEX returns the value at a position inside a range. =INDEX(B2:B5, 3) returns the 3rd value in B2:B5. MATCH finds the position of a value inside a range. =MATCH(“P103”, A2:A5, 0) returns 3, because P103 is the 3rd item in A2:A5. The 0 at the end means “exact match” — use it almost every time.
Put them together and MATCH supplies the position that INDEX needs:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
A worked example
Using the same small price list as our VLOOKUP and XLOOKUP tutorials — codes in A2:A5, prices in B2:B5:
| Code (A) | Price (B) |
|---|---|
| P101 | 250 |
| P102 | 400 |
| P103 | 175 |
| P104 | 520 |
With the code you want in D2 (say P103), the formula is:
=INDEX(B2:B5, MATCH(D2, A2:A5, 0))
Reading it aloud: “Find D2 in column A, take its position, and return the value at that position in column B.” The result is 175. Change D2 to P104 and you get 520. If there is no match, you get #N/A — wrap it in IFNA to show a friendly message instead: =IFNA(INDEX(B2:B5, MATCH(D2, A2:A5, 0)), “Not found”).
Advantage 1: It can look left
VLOOKUP can only return values from columns to the right of the lookup column. INDEX/MATCH does not care about direction. If prices were in column A and codes in column B, =INDEX(A2:A5, MATCH(D2, B2:B5, 0)) works exactly the same. No rearranging your table to suit the formula — the same advantage XLOOKUP offers, as covered in our XLOOKUP guide.
Advantage 2: Inserted columns cannot break it
VLOOKUP counts columns (“the price is the 2nd column”), so inserting a column between the lookup and return columns silently returns the wrong data. INDEX/MATCH points at real ranges, not a column number, so inserting a column just shifts the ranges and the answer stays correct. On shared spreadsheets, that robustness alone is worth switching for.
Advantage 3: Two-way lookups
With a second MATCH you can look up both a row and a column. Suppose a table has products down column A and months across row 1, with sales figures inside. =INDEX(B2:D5, MATCH(“P103”, A2:A5, 0), MATCH(“Mar”, B1:D1, 0)) finds the row for P103 and the column for Mar, and returns the figure where they meet. One formula, no helper columns.
The errors you might see
- #N/A — no exact match. Check for extra spaces or a different spelling in the lookup value, and confirm the 0 is there for exact match.
- #REF! — the return range and lookup range are different sizes, or a row/column was deleted. Make both ranges the same height.
- Wrong value, no error — almost always a missing 0 in MATCH, which allows an approximate match on sorted data. Always use 0 unless you deliberately want approximate matching.
INDEX/MATCH or XLOOKUP?
If your Excel is Microsoft 365 or Excel 2021 or newer, XLOOKUP is simpler to write. But INDEX/MATCH works in every version of Excel, including old ones, and in Google Sheets — so files built with it travel anywhere. Our VLOOKUP tutorial covers the classic formula, and XLOOKUP covers the modern one. Knowing INDEX/MATCH means you can read and fix almost any lookup formula you inherit.
Quick recap
=INDEX(where the answer lives, MATCH(what to find, where to look, 0)). It looks left or right, survives inserted columns, and with a second MATCH it does two-way lookups. For shared or older spreadsheets, it is still the safest lookup to use.
