📎 This article includes 1 downloadable practice file ↓
VLOOKUP is the first lookup most people learn, and it has three annoying limits: it only looks to the right, it breaks when someone inserts a column, and it is slow on big sheets. INDEX + MATCH fixes all three.
In this article
The two pieces
MATCH(what, where, 0)returns the position of a value in a list — for example 7 if it is the 7th item.INDEX(range, n)returns the n-th item of a range.
Put them together: find the position with MATCH, then fetch that position from another column with INDEX.
=INDEX(C2:C500, MATCH(F2, A2:A500, 0))Read it as: “give me the value in column C on the row where column A equals F2”.
Looking to the left
If the employee ID is in column D and you need the name from column B, VLOOKUP cannot do it. INDEX/MATCH does not care about direction:
=INDEX(B2:B500, MATCH(H2, D2:D500, 0))Two-way lookup (row and column)
For a table with months across the top and regions down the side:
=INDEX(B2:M20, MATCH("North", A2:A20, 0), MATCH("Mar", B1:M1, 0))Handle missing values
=IFERROR(INDEX(C2:C500, MATCH(F2, A2:A500, 0)), "Not found")Why it is safer than VLOOKUP
| VLOOKUP | INDEX/MATCH |
|---|---|
| Column number typed as 3 — breaks if a column is inserted | Points at the actual column — survives inserts |
| Right only | Any direction |
| Reads the whole table | Reads only two columns |
📎 Practice files for this article
- 📗Lookup scenarios workbookVLOOKUP, HLOOKUP, INDEX/MATCH and XLOOKUP side by side — vertical, horizontal, two-way — plus 6 real failure cases and their fixes, with PASS/FAIL checks.⬇ XLSX · 10 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.