Excel INDEX MATCH: The Better Alternative to VLOOKUP
INDEX MATCH can look up values in any direction, never breaks when you insert columns, and handles multiple conditions. Here's how it works and when to use it over VLOOKUP.
VLOOKUP has two annoying limitations: it can only look to the right, and it breaks if you insert a column inside your lookup range. INDEX MATCH solves both. It's more syntax to write upfront, but once you understand the pattern, you'll use it everywhere.
How it works
INDEX and MATCH are two separate functions combined:
MATCH finds the position of a value in a range:
=MATCH(lookup_value, lookup_array, [match_type])INDEX returns the value at a given position in a range:
=INDEX(array, row_num, [col_num])Combined, MATCH finds where your value is, and INDEX retrieves what you want from the same row:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))The 0 in MATCH means exact match — almost always what you want.
Step-by-step example
You have employee data: Column A = Employee ID, Column B = Name, Column C = Department, Column D = Salary. You want to look up salary by employee ID, where the ID column is not the first column.
VLOOKUP can't do this (it must search the leftmost column). INDEX MATCH can:
=INDEX(D2:D100, MATCH(G2, A2:A100, 0))D2:D100— the salary column (what to return)MATCH(G2, A2:A100, 0)— find row where column A matches the ID in G2- Result: the salary for that employee
Why it's better than VLOOKUP
| VLOOKUP | INDEX MATCH | |
|---|---|---|
| Look left | ✗ No | ✓ Yes |
| Insert column without breaking | ✗ Breaks | ✓ Safe |
| Speed on large datasets | Slower | Faster |
| Multiple condition lookup | ✗ Hard | ✓ Possible |
Looking up with two conditions
Combine two MATCH criteria using Ctrl+Shift+Enter (array formula in older Excel) or wrap in IFERROR:
=INDEX(C2:C100, MATCH(1, (A2:A100=G2)*(B2:B100=H2), 0))This returns the value in column C where both column A matches G2 and column B matches H2. In Excel 365, this works as a regular formula. In older versions, confirm with Ctrl+Shift+Enter.
✦ TIP
In Microsoft 365, XLOOKUP handles most cases where you'd previously use INDEX MATCH — and it's easier to write. INDEX MATCH is still the right choice when you need two-condition lookups or you're in a shared file with older Excel users.
The Griddy way
Lookup formulas are the most common source of spreadsheet errors — wrong range, off-by-one column index, forgetting the exact match argument. Describe the lookup and let Griddy build it:
"Look up the quarterly target for each salesperson by matching their name and the quarter column, then calculate whether they're above or below target"
Griddy writes the right formula type for your data structure, whether that's VLOOKUP, INDEX MATCH, or XLOOKUP.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.