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.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
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.
Use this on real templates
Use resilient lookups in operating trackers
INDEX MATCH is most valuable in sheets that change structure often, especially lead trackers, pipelines, and multi-owner ops boards.
CRM Lead Tracker
Track contacts, lead source, owner, next due date, and follow-up status in one lightweight CRM sheet. Keep hot opportunities and stale leads visible without paying for heavy sales software.
Sales Pipeline
Track deals by stage, owner, value, and next move in one lightweight pipeline sheet. Keep close dates, weighted forecast, and rep follow-ups visible without needing a full CRM.
Project Tracker
Track tasks, owners, priorities, due dates, and blockers in one delivery board. Group work by stream, review progress, and keep next steps visible.