INDEX and MATCH: The Lookup Pair That Survives a Moved Column

Every spreadsheet eventually contains a formula that used to be right. It returned the correct number for months, then someone added a column to the source table, and now it returns the phone number where the revenue used to be. No error, no warning — just a wrong number sitting in a report that people trust.

That formula was almost certainly a VLOOKUP, and the reason it broke is that it identifies the answer by counting columns. Column 4 means "four columns to the right of where I started", so the moment the shape of the table changes, the count is stale.

INDEX and MATCH fix this by splitting the lookup into two questions that are asked independently: where is it, and what's there. This guide covers both functions on their own, the pair working together, and the six or seven variations that cover almost every real lookup you'll ever need to write.

Tip: Follow along in your own workbook. Every example below runs on the table shown after section 1 — copy it into a blank sheet starting at A1 and the cell references will line up exactly.

1) Two Functions, One Job

A lookup formula does two things: it locates a row, then it reads a value from it. VLOOKUP bundles both into one function and hides the seam. INDEX and MATCH keep them separate, which is the entire point.

Neither is a lookup on its own. MATCH finds a row but can't read it; INDEX reads a row but can't find it. Nest one inside the other and you have a lookup that does everything VLOOKUP does, plus three things it can't:

That third row is honest rather than flattering. MATCH has the same approximate-by-default behaviour as VLOOKUP, and it catches people just as often. The difference is that everything else about the pair is safer.

2) MATCH: Position, Not Value

🎯 Scenario: Before looking anything up, find out which row a SKU is on.

=MATCH(lookup_value, lookup_array, [match_type])

Data Setup:

• Column A: Product
• Column B: Region
• Column C: SKU
• Columns D–F: Jan, Feb, Mar revenue

=MATCH("SKU-4107", C2:C9, 0)

Result: 7

Read that carefully: the answer is 7, not 8. MATCH counts inside the range you handed it, and C2:C9 starts at row 2 — so SKU-4107 is the seventh item of that range even though it sits on the eighth row of the sheet. This relative counting is the single most useful thing to internalise about MATCH, and the single most common source of off-by-one bugs.

The third argument decides how it searches:

Use 0 unless you are deliberately doing a banded lookup. Section 6 covers the case where 1 is the right answer; everywhere else it is a trap.

Pitfall: Omitting the third argument does not mean "exact". It means 1, which assumes your data is sorted ascending. On unsorted data that returns a plausible-looking wrong position with no error at all — the worst kind of failure, because nothing tells you it happened. Type the , 0 every single time.

Mini exercise: Find the position of "Mar" within the header range D1:F1. (You should get 3.)

3) INDEX: The Value at a Position

🎯 Scenario: Read the fourth product name without knowing what it is.

=INDEX(array, row_num, [column_num])

=INDEX(A2:A9, 4)

Result: Monitor 27

Give INDEX a two-dimensional range and it wants both coordinates:

=INDEX(D2:F9, 3, 3)

Result: 1440 — third row, third column of D2:F9, which is Monitor 27 North in March.

Same relative counting as MATCH: row 3 of D2:F9 is sheet row 4. The two functions agree on this, which is exactly why they nest so cleanly — a position from MATCH means the same thing to INDEX, as long as both ranges start on the same row.

INDEX does not search for anything. It's told where to look. That's a feature: it means the "where" can come from anywhere — a typed number, a cell, a calculation, or a MATCH.

Pitfall: =INDEX(A2:A9, 12) returns #REF! because there is no twelfth item. So does =INDEX(D2:F9, 3, 5) — three columns wide, no fifth column. If a working formula suddenly shows #REF!, the usual cause is a MATCH inside it that started returning a bigger number than the INDEX range can hold, which normally means the two ranges have drifted out of sync.

Mini exercise: Return the Jan figure on the eighth row of the data. (Expect 415.)

4) The Pair: Your First INDEX/MATCH

🎯 Scenario: January revenue for SKU-4107, without counting a single column.

=INDEX(D2:D9, MATCH("SKU-4107", C2:C9, 0))

Result: 640

Read it from the inside out, in two sentences: MATCH finds SKU-4107 in column C and reports position 7. INDEX returns the seventh value of column D. That's the whole pattern, and it never gets more complicated than that.

The sentence worth memorising: =INDEX(the column you want, MATCH(what you're looking for, the column to look in, 0)).

Now look left — the thing VLOOKUP cannot do at all:

=INDEX(A2:A9, MATCH("SKU-4104", C2:C9, 0))

Result: Monitor 27

Nothing special happened there. The return range is to the left of the search range, and neither function noticed or cared, because they were handed two independent ranges rather than one table with a counted offset. VLOOKUP genuinely cannot express this; the workaround is to physically move columns around in the source data, which is a strange thing to do to a table because of a formula.

And the reason the formula survives edits: insert a new column between B and C, and Excel updates both D2:D9 and C2:C9 to point at the same data as before. The equivalent =VLOOKUP("SKU-4107", C2:F9, 2, FALSE) still says 2, because 2 is a typed number and typed numbers don't move.

Pitfall: The INDEX range and the MATCH range must start on the same row and be the same height. =INDEX(D2:D9, MATCH("SKU-4107", C1:C9, 0)) includes the header in the search, so every position comes back one too big — and the formula returns the row below the right one. No error. Just quietly wrong, forever. Select both ranges the same way, every time.

Mini exercise: Return March revenue for SKU-4102. (Expect 2090.)

5) Two-Way Lookup: MATCH Twice

🎯 Scenario: Pick any SKU and any month, and get the number where they cross.

INDEX takes a row and a column. Feed both from a MATCH and you have a lookup that moves in two directions:

=INDEX(D2:F9, MATCH("SKU-4103", C2:C9, 0), MATCH("Mar", D1:F1, 0))

Result: 1440

The first MATCH walks down column C and finds row 3. The second walks across the header row and finds column 3. INDEX returns their intersection.

Make it an interface. Put the SKU in H1 and the month in H2:

=INDEX(D2:F9, MATCH(H1, C2:C9, 0), MATCH(H2, D1:F1, 0))

Now the sheet has two input cells and one answer, and nobody has to touch a formula to ask a different question. Add data validation dropdowns on H1 and H2 and it's a small report.

The alignment rule is the only thing to be careful about: D1:F1 must cover exactly the same columns as D2:F9. Search the headers of a four-column range with a three-column header range and the second MATCH will happily return positions that don't correspond to what INDEX is counting.

Pitfall: Header text is a common source of phantom #N/A. A header that reads "Mar " with a trailing space will never match "Mar", and the space is invisible on screen. Check with =LEN(F1) — if that returns 4 for a three-letter month, there's your problem. Clean the headers rather than padding the formula.

Mini exercise: Return the February figure for SKU-4108. (Expect 505.)

6) Approximate Match: Bands, Tiers and Grades

🎯 Scenario: A commission rate that depends on which revenue band a SKU lands in.

This is the one case where match_type 1 is not a mistake. Put a tier table in H2:I5, sorted ascending by threshold:

=INDEX(I2:I5, MATCH(D2, H2:H5, 1))

With D2 holding Laptop Pro North's January revenue of 2400, the result is 5% — 2400 doesn't reach 2500, so it falls into the band that starts at 1000.

What 1 actually does: it walks the list looking for the largest value that is still less than or equal to the lookup value, and stops there. That's why the table only needs the lower bound of each band — the next row's lower bound is this row's upper bound.

The sorting requirement is absolute. On an unsorted list, MATCH with 1 doesn't scan for the best answer; it uses a binary search that assumes order and gives up early. Sort the tier table ascending and leave it that way.

Use -1 when your table is naturally descending — grade boundaries written best-first, for example. Same idea, mirrored: the smallest value greater than or equal to the lookup.

Pitfall: Approximate match never returns #N/A for a value that's too large — 9,000,000 in the table above returns the 12% row quite happily. It only errors when the value is smaller than every entry. If your bands need an upper limit, add an explicit top row, or test for it with IF.

Mini exercise: Using the same tier table, what rate applies to SKU-4107's January revenue of 640? (Expect 0%.)

7) When Nothing Matches

🎯 Scenario: A SKU that isn't in the table, in a report other people read.

MATCH returns #N/A when it can't find the value, and INDEX passes that straight through. #N/A is a genuinely useful answer — it means "not found", which is different from zero — but it's ugly in a summary, and one #N/A poisons any SUM that touches it.

=IFNA(INDEX(D2:D9, MATCH(H1, C2:C9, 0)), "Not found")

Use IFNA, not IFERROR. IFERROR catches everything — #REF!, #VALUE!, #DIV/0! — which means it will cheerfully hide the fact that you broke your own formula. IFNA catches only "not found", so a real bug still surfaces as a real error.

When a value that's definitely there comes back #N/A, it's almost always one of four things:

1. The lookup value is text and the list is numbers (or the reverse). "4107" and 4107 are different values. =ISNUMBER(H1) versus =ISNUMBER(C2) settles it in five seconds.
2. Trailing or leading spaces, usually from a paste or an export. TRIM the source column into a helper column, then look up against that.
3. The ranges have drifted — you're searching C2:C9 but the data now runs to row 40.
4. Non-printing characters from a web or PDF paste. CLEAN handles most of them; =CODE(RIGHT(A2,1)) will show you the culprit.

A quick sanity check before you rewrite anything: =COUNTIF(C2:C9, H1). If that's 0, the formula is fine and the data is the problem.

Pitfall: MATCH is not case sensitive. "sku-4107", "SKU-4107" and "Sku-4107" all find the same row. That's usually convenient, and occasionally very much not — if you're looking up case-sensitive codes, you need EXACT inside an array formula instead.

Mini exercise: Wrap the section 4 formula so an unrecognised SKU returns "Unknown SKU" instead of #N/A.

8) Duplicates, Wildcards and the Last Match

🎯 Scenario: Two rows say "Laptop Pro". Which one does the formula give you?

=MATCH("Laptop Pro", A2:A9, 0)

Result: 1

Always the first. MATCH stops at the first hit and never looks further, so =INDEX(F2:F9, MATCH("Laptop Pro", A2:A9, 0)) returns 2210 — the North row — and gives no hint that a South row exists. This is the most expensive silent error in this article, because the answer looks completely reasonable.

Check before you trust it:

=COUNTIF(A2:A9, "Laptop Pro")

Result: 2

A count above 1 means "first match" is a decision you're making, not a fact about the data. Either add a second criterion (section 9) or say out loud which row you want.

To get the last match instead, there's a well-worn trick:

=INDEX(F2:F9, MATCH(2, 1/(A2:A9="Laptop Pro")))

Result: 2090 — the South row.

It looks like nonsense and it isn't. (A2:A9="Laptop Pro") produces TRUE/FALSE per row; dividing 1 by that gives 1 for the matches and #DIV/0! for everything else. MATCH with the default type 1 searches for 2, never finds it, and settles on the last value it can still use — which is the last 1, which is the last match. Note there's no third argument here; that's deliberate, and the only time in this article you'll want it missing.

Wildcards work with match_type 0 on text:

=MATCH("Dock*", A2:A9, 0)

Result: 7

Pitfall: Wildcards are text-only. =MATCH("*", D2:D9, 0) returns #N/A on a column of numbers even though every cell is full. And a lookup value that legitimately contains an asterisk needs "~*" or it will be read as a wildcard.

Mini exercise: Which product recorded a March figure of 690? (Use INDEX on column A with a MATCH on column F — expect Dock D3.)

9) Two Criteria at Once

🎯 Scenario: March revenue for Laptop Pro in the South — the row the previous section couldn't reach.

MATCH takes one lookup value, so the trick is to build a single array that already encodes both conditions.

Boolean multiplication is the more readable of the two ways:

=INDEX(F2:F9, MATCH(1, (A2:A9="Laptop Pro")*(B2:B9="South"), 0))

Result: 2090

Each comparison produces an array of TRUE/FALSE. Multiplying coerces them to 1 and 0, and multiplication acts as AND — a row scores 1 only if both conditions hold. MATCH(1, ..., 0) then finds the first such row. Add a third condition by multiplying in another bracket; nothing else changes.

Concatenation is the older approach:

=INDEX(F2:F9, MATCH("Laptop Pro"&"|"&"South", A2:A9&"|"&B2:B9, 0))

Result: 2090. The key is that the delimiter appears on both sides — the same "|" that glues the two ranges together has to glue the two halves of the lookup value together, or nothing will ever match.

Always use a delimiter. Without one, "AB"&"C" and "A"&"BC" produce the same string, and a lookup can match a row it has nothing to do with. A pipe or a tilde between the parts costs nothing and removes the whole class of bug.

Version note: in Microsoft 365 and Excel 2021 these just work. In Excel 2019 and earlier both forms are array formulas and need Ctrl+Shift+Enter — you'll see the formula wrapped in { } when it's been entered correctly.

Pitfall: Array-form criteria evaluate every cell in the ranges you name, so =INDEX(F:F, MATCH(1, (A:A="Laptop Pro")*(B:B="South"), 0)) asks Excel to process a million rows twice. One of these is imperceptible; thirty of them in a dashboard is a visible pause on every edit. Bound the ranges, or better, use a real Excel Table.

Mini exercise: Return January revenue for Monitor 27 in the North. (Expect 1280.)

10) INDEX Beyond Lookup — and Where XLOOKUP Fits

INDEX has two behaviours that have nothing to do with MATCH and are worth knowing on their own.

Pass 0 as a coordinate to get the whole row or column.

=SUM(INDEX(D2:F9, 5, 0))

Result: 1015 — the entire Q1 for row 5, Keyboard K2 North.

=SUM(INDEX(D2:F9, 0, 2))

Result: 8350 — the whole February column.

INDEX returns a reference, not just a value, which means it can sit on either side of a range colon:

=SUM(D2:INDEX(D2:D9, MATCH("SKU-4105", C2:C9, 0)))

That sums column D from the top of the data down to the SKU-4105 row, and the endpoint moves as the data changes. OFFSET can do the same thing, but OFFSET and INDIRECT are volatile — they recalculate on every single change anywhere in the workbook, whether or not it affects them. INDEX is not. On a large model that difference is measured in seconds per keystroke.

So should you still write INDEX/MATCH in 2026? Sometimes. XLOOKUP is genuinely better for the common case:

XMATCH is worth a mention on its own: it's MATCH with the defaults fixed. Exact match is the default rather than approximate, and it can search bottom-up with a fourth argument, which makes the section 8 last-match trick unnecessary: =XMATCH("Laptop Pro", A2:A9, 0, -1) returns 2 directly.

Pitfall: A workbook shared with someone on an older Excel will show _xlfn.XLOOKUP and #NAME? where your formulas were, and saving from that machine can strip them permanently. If you don't control every copy of the file, INDEX/MATCH is not nostalgia — it's compatibility.

Mini exercise: Total Q1 revenue for SKU-4105 using INDEX with a 0 coordinate and a MATCH for the row. (Expect 1015.)

Quick Checklist (Before You Trust the Answer)

• Every exact-match MATCH ends in , 0 — no exceptions outside section 6
• The INDEX range and the MATCH range start on the same row and are the same height
• Neither range includes the header row
• You've run COUNTIF on the key column to confirm there are no duplicates — or you've decided which duplicate you want
• Any approximate match (1 or -1) is pointed at a correctly sorted table
• Misses are wrapped in IFNA, not IFERROR
• Ranges are absolute ($C$2:$C$9) or a Table before the formula is filled down
• Multi-criteria concatenation uses a delimiter between the parts
• No whole-column references inside array-form criteria

Common Pitfalls Summary

1. Forgetting , 0: MATCH defaults to approximate. On unsorted data that's a wrong answer with no error attached.
2. Misaligned ranges: an INDEX range starting one row above the MATCH range returns the neighbouring row's value, permanently and silently.
3. Relative counting: MATCH returns the position within the range, not the sheet row. C2:C9 position 7 is sheet row 8.
4. Assuming the first match is the only match: COUNTIF the key column before you believe a duplicate-prone lookup.
5. Unsorted tier tables: approximate match uses a binary search and will return nonsense on unsorted data rather than erroring.
6. IFERROR instead of IFNA: hides your own #REF! and #VALUE! bugs alongside the genuine "not found".
7. Text-versus-number keys: "4107" never matches 4107. Check with ISNUMBER on both sides.
8. Trailing spaces: invisible, and fatal to an exact match. LEN exposes them.
9. Concatenating without a delimiter: "AB"&"C" equals "A"&"BC", so multi-criteria keys can collide.
10. Relative ranges when filling down: without $, the lookup range slides down the sheet row by row and the bottom rows find nothing.

Conclusion

The case for INDEX/MATCH was never that it's clever. It's that it describes what you actually mean. =INDEX(D2:D9, MATCH(H1, C2:C9, 0)) says find this in that column, and give me the matching value from this other column — three named ranges, no counting, no assumption that today's column order is permanent.

If you're on a current version of Excel and the file stays yours, write XLOOKUP and enjoy it. But the pair is still the answer whenever a file has to open somewhere older, whenever you need a position rather than a value, and whenever a range needs an endpoint that moves. Those cases have not gone away, and they're common enough that INDEX/MATCH is worth being fluent in rather than merely aware of.

The habit that matters most is the smallest one on the checklist: type the , 0. Every silent lookup failure in this article traces back to a formula that ran perfectly and answered a question nobody asked.

Want to practise? The lookup exercises in the app drill exactly these patterns — first match, two-way, and the ones where the obvious formula gives you the wrong row.