INDEX MATCH Explained (and Why It Beats VLOOKUP)
INDEX MATCH is a two-step lookup: MATCH finds which row your value is in, and INDEX returns whatever sits in that row of another column. Written out:
=INDEX($B$2:$B$100, MATCH(D2, $A$2:$A$100, 0))That reads as: find D2 somewhere in column A, note which position it's in, then give me the value at that same position in column B.
It looks more complicated than VLOOKUP, and for about ten minutes it is. What you get in exchange is a lookup that searches in any direction and doesn't break when someone inserts a column — the two failures that eventually catch out every VLOOKUP user.
Understanding the two halves
The combination is easier to learn if you build it in two steps rather than reading it as one formula.
MATCH returns a position, not a value
=MATCH("PRD-102", $A$2:$A$100, 0)If PRD-102 is the second entry in that range, this returns 2. Not the price, not the product name — just the number 2. The 0 at the end means exact match, and it is the same trap as VLOOKUP's FALSE: leave it out and MATCH switches to approximate mode.
INDEX returns a value at a position
=INDEX($B$2:$B$100, 2)This gives you the second value in column B. It has no idea what you were searching for — it just counts down to position 2 and reports back.
Nesting them
MATCH produces the number INDEX needs, so you put one inside the other:
=INDEX($B$2:$B$100, MATCH(D2, $A$2:$A$100, 0))If you're ever unsure why a result looks wrong, split it back into two cells. Run the MATCH on its own and check the position it returns. That single habit solves most INDEX MATCH problems in under a minute.
MATCH finds the position, INDEX returns the value there — and the answer can sit to the left.
Why it beats VLOOKUP
1. It searches in any direction
VLOOKUP can only return columns to the right of the one it searches. INDEX MATCH has no such rule, because the two ranges are unrelated to each other.
Say product codes sit in column C and product names in column A:
=INDEX($A$2:$A$100, MATCH(D2, $C$2:$C$100, 0))VLOOKUP cannot do this at all. You'd have to physically move columns around, which usually means breaking something else.
2. Inserted columns don't break it
A VLOOKUP's column index is a number you typed. Insert a column inside the range and the number now points at the wrong data — and the formula keeps calculating, so nothing tells you.
INDEX MATCH refers to real ranges. Insert a column and Excel shifts the references with it, the way it does everywhere else in the program. This is the practical reason experienced users move over: it's not that INDEX MATCH is more powerful, it's that it fails loudly instead of quietly.
3. It works in every version of Excel
Both functions have shipped in Excel for decades. Unlike XLOOKUP, an INDEX MATCH file opens correctly in Excel 2016, in Excel 2010, in Google Sheets, and in most third-party tools that read spreadsheets.
4. It can be faster in big files
VLOOKUP scans every column of the range you hand it. INDEX MATCH only touches two columns. On a small sheet this is irrelevant; on a workbook with thousands of lookups across wide tables, it's noticeable.
A worked example
Columns A to C hold a product list — name, category, code — and you want the product name for a code you've typed into E2. The code is in the last column, and the name is in the first.
| A — Product | B — Category | C — Code |
|---|---|---|
| Steel bracket | Fixings | PRD-101 |
| Nylon washer | Fixings | PRD-102 |
| Copper pipe | Plumbing | PRD-103 |
=INDEX($A$2:$A$4, MATCH(E2, $C$2:$C$4, 0))Type PRD-102 into E2 and you get Nylon washer. A VLOOKUP has no way to produce this result, because the answer lies to the left of the search column.
Two-way lookup: INDEX MATCH MATCH
Once you're comfortable, a second MATCH lets you pick both the row and the column — useful for rate tables, price matrices and timetables.
=INDEX($B$2:$E$50, MATCH($G$1, $A$2:$A$50, 0), MATCH($G$2, $B$1:$E$1, 0))The first MATCH finds the row from the labels down the left. The second finds the column from the headers across the top. INDEX returns whatever sits where they cross.
Errors and what causes them
#N/A
MATCH couldn't find the value. Usual causes, in order of likelihood: a trailing space, a number stored as text, or the value genuinely isn't there. Wrap the lookup value in TRIM() to rule out the first — the same fix that solves the most common VLOOKUP failure.
Wrong row returned
Almost always the missing 0 in MATCH. Without it, MATCH assumes your data is sorted and returns the closest lower match.
#REF!
INDEX was asked for a position beyond the end of its range. This happens when the INDEX range and the MATCH range are different heights — for example $B$2:$B$50 paired with $A$2:$A$100. Keep both ranges the same number of rows.
#VALUE!
Usually a text value where MATCH expects a number, or arguments in the wrong order. Split the formula in two and test the MATCH alone.
Which one should you actually use?
| Situation | Use |
|---|---|
| Quick lookup, answer is to the right, stable sheet | VLOOKUP |
| Answer is to the left, or columns get inserted | INDEX MATCH |
| Everyone involved is on Microsoft 365 | XLOOKUP |
| Sharing with older Excel versions | INDEX MATCH |
| Looking up by row and column at once | INDEX MATCH MATCH |
Common questions
Is INDEX MATCH harder to learn than VLOOKUP?
Slightly, and only at the start. Build it in two cells for the first few attempts and it stops feeling like one complicated formula.
Does it work in Google Sheets?
Yes, identically. Same names, same arguments, same behaviour.
Should I replace my existing VLOOKUPs?
Not as a project. Use INDEX MATCH for new work, and convert an old formula when it breaks or when you need it to look left.
Can I add a fallback for missing values?
Yes, the same way as VLOOKUP: =IFERROR(INDEX(...), "Not found").
The short version
- MATCH finds the position; INDEX returns the value at that position.
- Always end MATCH with
0for exact matching. - Keep the INDEX range and the MATCH range the same height.
- Split the formula into two cells whenever a result looks wrong.
- It works in every Excel version, which is what makes it the safest lookup to leave in a shared file.
If everyone opening your files is on Microsoft 365, XLOOKUP does all of this with less typing — it's worth knowing which of the three fits your situation before you commit a whole workbook to one of them.
Comments
Post a Comment