INDEX MATCH vs VLOOKUP: The Lookup That Survives a New Column

1. MATCH on its own: a name in, a number out

Before the explanation: MATCH is going to look for "Lamp" in a three-row list where Lamp is last. Say what you think it hands back.

=MATCH("Lamp", Products[Product], 0)

3

Not "Lamp", not "Lighting", not 40. Just 3. MATCH's entire job is to report where something is, counting from the top of the range you gave it. Lamp is the third entry, so the answer is 3.

Two of its three arguments are obvious: what to find, and where to look. The third one is the important one. 0 means exact match, and it is the only setting you should be typing until you have a specific reason not to. Section six is about what the other settings do to you.

A number on its own looks useless, and that impression is the reason people skip MATCH and reach for a lookup that does everything in one go. Hold on to the number. It is the thing that makes the rest flexible.

2. INDEX on its own: a number in, a value out

Before the explanation: given the Products table above, say what INDEX(Products[Category], 3) returns before you read the output.

=INDEX(Products[Category], 3)

Lighting

INDEX takes a range and a position and hands back what is sitting at that position. It does no searching at all. Give it 3 and it reads the third cell, whether or not the third cell is the one you wanted.

So INDEX is deliberately dumb and MATCH is deliberately narrow, and neither is much use alone. Each knows exactly one thing: MATCH knows where, INDEX knows what is there.

3. Putting them together, and the sentence to say while typing

Feed MATCH's number straight into INDEX's second argument.

=INDEX(Products[Category], MATCH("Lamp", Products[Product], 0))

Lighting

Read it out loud in the order it runs, from the inside out, and it stops looking like a nest of brackets: "find Lamp in the Product column, exactly, and give me whatever is in the same position of the Category column." That sentence has two column names in it, and so does the formula. Every INDEX MATCH you ever write has that same shape.

The order on the page is the reverse of the order it runs, which is the one genuinely awkward thing about it. So type it inside out. Write the MATCH first, on its own, and confirm it returns a sensible number. Then wrap INDEX( around it. Debugging a lookup that was built in two steps takes seconds, because you can always delete the INDEX and see what position MATCH thinks it found.

The same pair works on a real column of the orders table. Order 1001 is a Desk, so pulling its category in:

=INDEX(Products[Category], MATCH([@Product], Products[Product], 0))

Furniture

4. The answer that sits to the left

Before the explanation: try to write a VLOOKUP that starts at Product and returns Category, using the Products table exactly as it is printed above. Give it thirty seconds.

There is no way to write it. VLOOKUP takes a block of columns, searches the first column of that block, and counts rightward to the answer. Category is to the left of Product, so it is outside every block that starts at Product. Asking for a column number of 0 or −1 does not reverse the direction; both simply fail, and in my run both returned #N/A.

=VLOOKUP("Lamp", Products, -1, FALSE)

#N/A

INDEX MATCH has no direction, because the searching and the fetching happen in different arguments. MATCH gets pointed at Product and INDEX gets pointed at Category, and whether Category is to the left, to the right, or on another sheet entirely makes no difference to either of them.

The usual workaround is to rearrange the reference table so the key is first. Sometimes that is fine. Often the table is somebody else's, refreshed from a system, or used by three other workbooks that expect the current layout, and quietly moving its columns is how you break somebody else's Monday.

5. The hard-coded number that goes stale

This is the failure that actually costs money, so here it is as a run rather than a warning. Three formulas, all asking the same question, what does a Lamp cost us:

=VLOOKUP("Lamp", Products[[Product]:[Cost]], 3, FALSE)      22
=INDEX(Products[Cost], MATCH("Lamp", Products[Product], 0))   22
=XLOOKUP("Lamp", Products[Product], Products[Cost])           22

All three agree. Now somebody adds a Supplier column to the reference table, between ListPrice and Cost. Nothing else changes. Same three formulas, untouched:

=VLOOKUP("Lamp", Products[[Product]:[Cost]], 3, FALSE)      Northlight
=INDEX(Products[Cost], MATCH("Lamp", Products[Product], 0))   22
=XLOOKUP("Lamp", Products[Product], Products[Cost])           22

The VLOOKUP now returns a supplier name where a cost used to be. It did nothing wrong: it was told to count three columns across, it counted three columns across, and the third column is Supplier now. The number 3 was a fact about the layout on the day it was typed, frozen into the formula.

Say out loud what the damage looks like if the inserted column had been numeric instead of text. Text at least stands out; Northlight in a cost column is visible. A second price column in the same slot would have produced a wrong number that formats, sums and charts exactly like a right one, and the workbook would have looked perfectly healthy.

INDEX MATCH survives because nothing in it refers to a position that a human chose. Products[Cost] means the Cost column wherever the Cost column now is. If you would rather keep VLOOKUP, you can get most of the way there by replacing the number with a MATCH on the header row, MATCH("Cost", Products[#Headers], 0), which is the single highest-value edit in this whole guide.

6. The fourth argument that returns a wrong answer with no error

Before the explanation: our Products table lists Desk, Chair, Lamp in that order, which is not alphabetical. Here is a VLOOKUP for the price of a Desk with the last argument set to TRUE. The right answer is 220. Predict what it returns.

=VLOOKUP("Desk", Products[[Product]:[Cost]], 2, TRUE)

85

85 is the price of a Chair. No error, no warning, no colour. That is what the last argument does when it is TRUE, which is also what it does when you leave it out entirely.

TRUE means approximate match, and approximate match makes one assumption: that your lookup column is sorted ascending. Under that assumption it can stop early and take the last value it passed that was not too big, which is genuinely useful for banding, where you want "whatever bracket this number falls into". Point it at an unsorted list and the assumption is false, so it stops in the wrong place and reports whatever was there with complete confidence.

The lesson is not that TRUE is bad. It is that a lookup has two different jobs, and they need different settings. Finding a specific known key is an exact-match job, and needs FALSE in VLOOKUP or 0 in MATCH, typed every single time. Finding which band a number falls into is an approximate-match job, and needs a sorted table you built on purpose.

MATCH has the same three-way setting, and the same trap: its third argument defaults to 1, which is approximate. Typing the 0 is not optional politeness. It is the difference between an answer and a guess.

7. Two-way lookup: a row and a column at once

INDEX's second argument is a row position. It also takes a third argument, a column position, and both of them can be a MATCH. That turns any grid into something you can query by two labels.

=INDEX(Products[[ListPrice]:[Cost]],
       MATCH("Chair", Products[Product], 0),
       MATCH("Cost",  Products[[#Headers],[ListPrice]:[Cost]], 0))

52

Read it the same way: find the Chair row, find the Cost column, hand me the cell where they cross. Change either word and the formula follows. Put those two words in cells and let somebody type into them, and you have built a small lookup tool with no macros and nothing to maintain.

This is the case that still belongs to INDEX MATCH, because a two-way lookup is not something VLOOKUP can express at all, and XLOOKUP needs to be nested inside itself to manage it. One INDEX with two MATCHes is easier to read than any of the alternatives.

Picture your own reference tables for a second: a rate card, a shipping matrix, a tax table, a grade boundary sheet. Which one of them do you currently look things up in by scrolling and pointing at the screen? That is the one to convert first.

8. What MATCH is fussy about, and what it forgives

Two facts, both worth knowing exactly, both checked rather than assumed.

Case does not matter. Searching for "lamp" in a list containing Lamp returns 3, the same as searching for "Lamp". Excel's text comparison here ignores capitals, so a key that arrives in a different case still finds its row.

Spaces matter completely. Searching for "Lamp ", with one trailing space, returns #N/A.

=MATCH("lamp",  Products[Product], 0)      3
=MATCH("Lamp ", Products[Product], 0)      #N/A

That pair explains most mystery lookup failures. The value on screen looks identical to the value in the reference table, because a trailing space is invisible, and the formula is quite correct that they are different strings. Before rewriting a lookup that "should work", wrap the key in TRIM() and see whether the problem disappears.

A third mismatch is just as common and even harder to see: a key that is a number in one table and text in another. 1001 and "1001" never match. The tell is that the failing keys are always the numeric-looking ones, and the fix is to make one side agree with the other rather than to keep adjusting the formula. When the keys differ in messier ways than spaces and capitals, that is a bigger job with a name: entity resolution.

9. XLOOKUP, and when INDEX MATCH still earns its place

XLOOKUP does this job in one function, defaults to exact match, and takes the lookup column and the return column as two separate arguments, which is the same separation that makes INDEX MATCH robust.

=XLOOKUP("Lamp", Products[Product], Products[Category])

Lighting

If XLOOKUP is available to you and to everyone who opens your file, use it for ordinary one-directional lookups. It is shorter and its defaults are the safe ones.

Three situations still go to INDEX MATCH. A workbook that has to open in an older Excel, or in another spreadsheet program, where XLOOKUP is not there and the formula arrives as #NAME?. A two-way lookup, from section seven. And reading somebody else's model, which is the honest reason to learn it even if you never type it: INDEX MATCH is in millions of live workbooks and you will be asked to change one.

None of the three lookups protects you from a key that genuinely is not there. All of them return #N/A, which is the correct behaviour, and hiding it is its own mistake. What to wrap around it, and when hiding an error is the wrong move, is a page of its own.

Edge cases that break a lookup quietly

Six that each cost somebody an afternoon.

The key appears twice in the reference table. MATCH, VLOOKUP and XLOOKUP all stop at the first hit and never mention the second. If your reference table can contain duplicates, count them before you trust the lookup: COUNTIF on the key column, and anything above 1 is a decision you have to make rather than one Excel should make for you.

The ranges are different lengths. INDEX on a 4-row column with MATCH over a 5-row column will happily return the wrong row, or #REF! if the position runs off the end. Point both at whole named columns and they cannot drift apart.

Half-locked references. Copying MATCH(A2, B2:B4, 0) down the sheet slides the lookup range to B3:B5, so the last rows search a range that is missing entries. Lock it with $B$2:$B$4, or use a named table, where the range cannot slide at all.

An #N/A that got hidden too early. Wrapping the lookup in something that turns errors into a blank or a zero makes the sheet look clean and makes an unmatched key invisible. Count the misses first, decide what they mean, and only then decide what to display.

Lookups against a whole column on a large table. MATCH(A2, D:D, 0) scans a million cells per row. On a few hundred rows nobody notices; on fifty thousand the file starts taking seconds to recalculate. Point at the table, not the column letter.

A reference table that grew. A fixed range like $B$2:$B$4 never sees a fourth product, so new items silently return #N/A forever. This is the same argument as Ctrl+T everywhere else in this set of guides: a named table grows, a typed range does not.