How to Use INDEX MATCH in Excel (with Examples)
INDEX MATCH is the flexible lookup that fixes VLOOKUP's biggest limits — it can look to the left and it does not break when you add columns. Here is exactly how INDEX and MATCH work together, step by step.
INDEX MATCH is the lookup that experienced Excel users reach for instead of VLOOKUP. It does everything VLOOKUP does, plus two things VLOOKUP cannot: it can look to the left of your search column, and it does not break when you insert or move columns. Once it clicks, you will rarely go back.
The trick is that it is really two functions working together. Let us take them one at a time, then combine them.
Step 1: How MATCH finds the position
MATCH looks down a range and returns the position number of a value — not the value itself. Its syntax is:
=MATCH(lookup_value, lookup_range, 0)The 0 means "exact match" — always use it for lookups. If "Widget" is the 4th item in A2:A100, then =MATCH("Widget", A2:A100, 0) returns 4.
Step 2: How INDEX returns the value
INDEX does the opposite — you give it a position, and it returns the value there:
=INDEX(return_range, row_number)So =INDEX(B2:B100, 4) returns the 4th value in column B. On its own that is not very useful — you rarely know the row number by hand. That is exactly the gap MATCH fills.
Step 3: Combine them
Drop MATCH inside INDEX where the row number goes. MATCH finds the position, and INDEX returns the value from that position:
=INDEX(B2:B100, MATCH("Widget", A2:A100, 0))| Part | Job |
|---|---|
| MATCH("Widget", A2:A100, 0) | Finds which row "Widget" is on |
| INDEX(B2:B100, ...) | Returns the value from column B on that row |
Why use it instead of VLOOKUP?
- It looks left. VLOOKUP can only return values to the right of the search column. INDEX MATCH can return a value from any column, in either direction.
- It survives edits. VLOOKUP breaks when you insert a column, because its column number is hard-coded. INDEX MATCH points at real ranges, so it keeps working.
- It can be faster. On large sheets, INDEX MATCH reads only the two columns it needs, not the whole table.
Bonus: a two-way lookup
Use MATCH twice — once for the row, once for the column — to find a value at the intersection of both:
=INDEX(B2:E100, MATCH("Widget", A2:A100, 0), MATCH("Q3", B1:E1, 0))This finds the row for "Widget" and the column for "Q3" and returns the number where they meet. It is the same idea you would reach for after mastering VLOOKUP in Excel. Combine it with an IF function for extra logic, or do a similar lookup in Google Sheets with VLOOKUP in Google Sheets.
Frequently asked questions
Is INDEX MATCH better than VLOOKUP?
For most real work, yes. INDEX MATCH can look to the left, does not break when you add or move columns, and can be faster on large sheets. VLOOKUP is simpler to type, so it is still fine for quick, one-off lookups on a stable table.
What does the 0 mean in MATCH?
The 0 tells MATCH to find an exact match. Without it, MATCH assumes your data is sorted and looks for the closest lower value, which gives wrong results on unsorted lists. For lookups, always use 0.
Why does my INDEX MATCH return #N/A?
#N/A means MATCH could not find the value. Check for trailing spaces, text stored as numbers, or a lookup range that does not include the value. Make sure the value you are searching for exactly matches the data.
Can I use INDEX MATCH across two sheets?
Yes. Point the ranges at the other sheet, like =INDEX(Data!B2:B100, MATCH(D1, Data!A2:A100, 0)). The formula works exactly the same way when the lookup table lives on a different tab.