INDEX MATCH is the lookup combination that Excel power users swear by. It looks scarier than VLOOKUP but it's actually simpler once you understand what each part does.
If you've been using Excel for a while, you've probably heard of INDEX MATCH. It gets mentioned by advanced Excel users as the "better VLOOKUP" — but most people never actually learn it because it looks intimidating.
Here's the truth: INDEX MATCH isn't complicated. It's just two formulas working together. Once you understand what each one does separately, combining them is obvious.
VLOOKUP has one fundamental limitation: the thing you're looking for must always be in the leftmost column of your table. You can only return values to the right of it.
This means if someone restructures your data — or if you need to look up something and return a value to its left — VLOOKUP breaks. INDEX MATCH has no such limitation. It can look in any direction.
INDEX returns the value at a specific position in a range. Think of it like navigating a grid — you tell it which row and which column, and it gives you the value there.
=INDEX(array, row_number, [column_number])
Simple example: what's in row 3 of the range A1:A10?
=INDEX(A1:A10, 3)
← returns whatever is in A3
On its own, INDEX isn't that useful — you already know what's in A3. The power comes when you combine it with MATCH.
MATCH finds the position of a value in a list. It returns a number — the row (or column) where the value appears.
=MATCH(lookup_value, lookup_array, match_type)
Example: where does "Coffee" appear in column A?
=MATCH("Coffee", A1:A10, 0)
← returns 3 if Coffee is in A3. Use 0 for exact match.
MATCH doesn't return the value itself — it returns the position. The 0 at the end means exact match (which is almost always what you want).
Now put them together: use MATCH to find the position, feed that into INDEX to get the value at that position.
=INDEX(B1:B10, MATCH("Coffee", A1:A10, 0))
Read this as: "Find where Coffee appears in column A (that's MATCH), then return the value at that same position in column B (that's INDEX)."
MATCH does the finding. INDEX does the returning. Feed MATCH's result into INDEX and you've built a lookup that works in any direction.
=INDEX(C2:C10, MATCH(E2, A2:A10, 0))
← E2 has the product name. Return its price from column C.
Your product codes are in column C and names are in column A. VLOOKUP can't go left — INDEX MATCH can.
=INDEX(A2:A10, MATCH("PRD-003", C2:C10, 0))
← find the product code in C, return the name from A (to the left)
Return the name of whoever scored highest — combine with MAX.
=INDEX(A2:A10, MATCH(MAX(B2:B10), B2:B10, 0))
← finds the max score, returns the name of that person
=IFERROR(INDEX(B2:B10, MATCH(E2, A2:A10, 0)), "Not found")
Both INDEX MATCH and XLOOKUP are genuinely useful. Learning INDEX MATCH is worth it even if you use XLOOKUP day to day — because eventually you'll encounter a spreadsheet that needs it, and you'll be glad you know it.
ExcelPro has INDEX MATCH exercises built into every specialist track. Type real formulas, get instant feedback.
Try it free →