Your lookup formula should find the right value no matter which column it sits in. That is exactly what INDEX MATCH in Excel is for — and unlike VLOOKUP, it keeps working when someone inserts or moves a column. In this guide you will build six working examples on the same small datasets, check the exact output of each one, and learn the few mistakes that cause most #N/A errors.

One pattern behind every INDEX MATCH formula

Every version of this lookup — vertical, horizontal, two-way — is built from the same two pieces. MATCH finds the position of your key in a list, and INDEX returns whatever value sits at that position:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Part What it does Sample value
return_range The column (or row) where the answer lives B2:B100
lookup_value The key you are searching for E2, holding “SKU-104”
lookup_range The column Excel scans for the key A2:A100
0 Exact match — almost always what you want —

The two ranges must be the same length and aligned row for row. That single rule prevents most of the problems in this article.

Example 1 — the classic vertical lookup

Start with a small inventory sheet. Keys sit in column A; the values you may want live to the right:

A — ID B — Price C — Stock
SKU-101 12.99 340
SKU-102 8.50 210
SKU-103 42.00 18
SKU-104 24.50 152
SKU-105 5.75 610

Put SKU-104 in cell E2 and enter this in F2:

=INDEX(B2:B6, MATCH(E2, A2:A6, 0))

Result: 24.50. Why it works: MATCH(E2, A2:A6, 0) scans the five IDs, finds “SKU-104” in the fourth position, and returns the number 4. Then INDEX(B2:B6, 4) picks the fourth cell of the price column. Swap B2:B6 for C2:C6 and the same formula returns 152 units of stock — the formula does not care which column holds the answer, as long as both ranges start on the same row.

Example 2 — looking left, where VLOOKUP simply cannot

Now flip the layout. The key lives in column C and the value you want in column A — something that happens constantly when a colleague reorders a report:

A — Region B — Sales C — Code
North 48,200 R-01
South 36,750 R-02
East 51,900 R-03

VLOOKUP can only look right, so it cannot reach column A from column C — you’d normally add a helper column just to flip the table. INDEX MATCH needs no copy-paste:

=INDEX(A2:A4, MATCH("R-02", C2:C4, 0))

Result: South. MATCH finds “R-02” at position 2 in C2:C4; INDEX(A2:A4, 2) returns the region in that same row. Because MATCH searches its own range and INDEX reads its own, the two can point in opposite directions. If you are comparing this against XLOOKUP and VLOOKUP side by side, this left-look is usually the deciding feature.

Example 3 — the two-way lookup (row and column at once)

The pattern scales to two dimensions. Say the grid below is monthly revenue, with product names in A2:A4, month headers in B1:E1, and the numbers in B2:E4:

Feb Mar Apr May
Widgets 12,100 13,400 12,800 14,900
Bikes 8,600 9,900 11,200 10,400
Cables 4,300 4,100 4,750 5,200

To pull Bikes for April you need the right row and the right column, so nest one MATCH in each axis of INDEX:

=INDEX(B2:E4, MATCH("Bikes", A2:A4, 0), MATCH("Apr", B1:E1, 0))

Result: 11,200. The row MATCH finds “Bikes” at position 2; the column MATCH finds “Apr” at position 3; INDEX returns the cell at row 2, column 3 of B2:E4. Change either word and you read any other cell in the grid — the basis of many small dashboards. The same idea, combined with date-based criteria, shows up in date-range summing with SUMIFS, where the criterion does the matching instead.

Example 4 — exact text matching, the 0 you must never drop

Text keys behave like IDs, and matching is case-insensitive: searching “r-02” still finds “R-02”. The trap is the third argument. Excel’s MATCH defaults to match type 1 (approximate) when you omit it — a mode designed for ascending-sorted numbers. Against text, it either errors or returns the first row, which is silently wrong.

=INDEX(B2:B4, MATCH("r-02", C2:C4, 0))

Result: 36,750 — the South sales figure from the table above. Note that MATCH searches column C while INDEX reads column B; the 0 at the end is what guarantees an exact hit on “R-02”. Rule of thumb: in exact-match lookups, write the 0 even though Excel forgives you omitting it.

Example 5 — tiered tables: the one approximate match worth knowing

Match type 1 is legitimate for “highest band that qualifies” tables — commission tiers, tax brackets, pricing steps. The only hard requirement: the key column must be sorted ascending.

A — Annual sales B — Rate
0 2.0%
20,000 3.0%
50,000 4.5%
100,000 6.0%

A rep closed 74,300 in annual sales, which lands in the 50,000 band:

=INDEX(B2:B5, MATCH(74300, A2:A5, 1))

Result: 4.5%. Match type 1 finds the last value less than or equal to 74,300 — that is 50,000, position 3 — and INDEX returns the rate at that position. It is “fuzzy matching done deliberately”: 74,300 does not appear in the table, yet the formula snaps to the correct band. Run the same formula on an unsorted table and you get a plausible but wrong rate, with no error at all.

Example 6 — the position itself is the answer

Sometimes you only need to know where something is. With invoice numbers in A2:A6 — INV-2198, INV-2204, INV-2207, INV-2210, INV-2215 — MATCH alone finds the slot:

=MATCH("INV-2210", A2:A6, 0)

Result: 4 — the fourth item in the range. Add + 1 and you get the real worksheet row (5), which is handy for building references like "Sheet1!A"&(...). And a pure presence test is one function:

=ISNUMBER(MATCH(E2, A2:A6, 0))

Result: TRUE when the key exists, FALSE when it does not — no error, no IFERROR needed. Pair that with a COUNTIF and you can flag duplicates across a list.

Wrapping lookups so they never show #N/A

Any of the formulas above returns #N/A when the key is missing. For a client-facing sheet, wrap the whole thing:

=IFERROR(INDEX(B2:B6, MATCH(E2, A2:A6, 0)), "Not found")

Result: “Not found” when SKU-104 were absent, and 24.50 when it exists. This wrapper is cheap insurance and costs nothing in readability.

Five mistakes behind most broken INDEX MATCH formulas

  1. Mismatched range lengths. If MATCH scans A2:A100 but INDEX reads B3:B101, every result is off by one row. Lock both ranges to the same first and last row.
  2. Dropping the 0. Omitted match type means approximate mode, which misbehaves on text and unsorted data. Always type it in exact-match lookups.
  3. Approximate match on an unsorted table. Match type 1 is only trustworthy on an ascending key column — an unsorted tier table returns a silent wrong value.
  4. Text masquerading as a number. A key stored as “104” will never match the number 104 in exact mode. Fix the source with =VALUE() or =TRIM() rather than patching the lookup.
  5. Copying the formula sideways without checking references. Relative references shift when you drag the formula across columns; if the ranges should stay fixed, convert them to absolute references first.

Frequently Asked Questions

What is the difference between INDEX MATCH and VLOOKUP?

VLOOKUP only looks to the right and breaks when you insert a column before the values. INDEX MATCH reads either direction, tolerates column insertions, and supports two-way lookups — at the cost of a slightly longer formula. A full head-to-head, including seven real examples, is in the XLOOKUP vs VLOOKUP guide.

Do I need a recent version of Excel for INDEX MATCH?

No. Both functions are decades old and work in every modern version, from Excel 2007 through Microsoft 365. You do not need dynamic arrays, LAMBDA, or any premium feature — this is as portable a lookup pattern as Excel gets.

Why does my INDEX MATCH formula return #N/A when the value is clearly there?

Three usual suspects: a stray space or typo in the key (wrap it with TRIM() and check), a text/number type mismatch (compare with =ISTEXT(key)), or an accidental approximate match type. Exact mode (0) plus a cleaned key resolves the majority of these.

Can INDEX MATCH return multiple results at once?

Not by itself — it returns one cell per formula. If you need every row that matches a criterion, reach for FILTER in Microsoft 365, or combine INDEX MATCH with COUNTIF to test individual candidates. For structured lookups and condition-based sums, the SUMIFS date-range guide shows the criterion-based approach.

How is INDEX MATCH different from XLOOKUP?

XLOOKUP wraps the same search in one cleaner function and can look both directions, but it is a Microsoft 365 / Excel 2021 feature. INDEX MATCH remains the portable choice for anything you might send to a colleague on an older build — and the underlying logic is identical, so switching between them is trivial.

Bottom line

INDEX MATCH is one pattern, not a menu of tricks: MATCH finds a position, INDEX reads a value, and the two ranges must line up. Master the exact-mode version and the tiered-table version, and you cover nearly every lookup a spreadsheet job throws at you — with none of the fragility that keeps VLOOKUP breaking.

Want to skip the guesswork? Describe what you need in plain English and let ai-excel.net generate the formula for you — then paste it straight into your sheet. And if you are newer to formula writing in general, start with how to write Excel formulas with AI.

Related reading