🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel INDEX & MATCH — The Two-Function Lookup That Beats VLOOKUP

|

Excel INDEX & MATCH — The Two-Function Lookup That Beats VLOOKUP

TL;DR — INDEX/MATCH is a lookup built from two functions that each do one job. MATCH(value, lookup_column, 0) returns the position of a value in a column (which row). INDEX(return_column, position) returns the value at that position. Nest them — =INDEX(Return, MATCH(key, Lookup, 0)) — and you get a lookup that VLOOKUP can't match: it looks left or right, survives inserted columns, and extends to a true row-and-column lookup. The one trap is MATCH's third argument — use 0 for exact match. Works in every version; in 365, XLOOKUP folds both into one.

=INDEX(C2:C500, MATCH(F2, A2:A500, 0))     ' find F2 in A, return the matching C
=INDEX(Price, MATCH("APX-100", ID, 0))     ' named ranges read like a sentence

The reason spreadsheet veterans reach for INDEX/MATCH isn't nostalgia — it's that it separates two things VLOOKUP welds together: finding a row and fetching a value. MATCH does the finding. INDEX does the fetching. Because neither one cares where the other's column sits, the whole rigid geometry of VLOOKUP — value-on-the-left, count-columns-to-the-right — simply evaporates.

What you'll learn

  • The mental model: MATCH finds where, INDEX fetches what
  • Why decoupling means you can look left, not just right
  • Why INDEX/MATCH survives inserted columns when VLOOKUP silently breaks
  • The match_type trap — the same "use 0" lesson as VLOOKUP's fourth argument
  • The move nothing else does cleanly: a two-way (row × column) lookup
  • When XLOOKUP replaces it — and when INDEX/MATCH still wins

The mental model: MATCH finds where, INDEX fetches what

Run each half on its own and the division of labour is obvious.

=MATCH("APX-100", A2:A500, 0)    ' -> 37   (APX-100 is the 37th item in column A)
=INDEX(C2:C500, 37)              ' -> the value in the 37th cell of column C

MATCH searches a single column and hands back a number: the position of your value. INDEX ignores searching entirely — give it a range and a position and it returns the value sitting there. Neither knows about the other. You bolt them together by feeding MATCH's position straight into INDEX:

=INDEX(C2:C500, MATCH("APX-100", A2:A500, 0))
'       ^fetch from here      ^find the row here

That's the entire technique. And notice what's not in it: no column-count, no requirement that the two ranges are next to each other or in any particular order.

Decoupling means you can look left

Because the search range and the return range are independent arguments, their left-to-right order is irrelevant. Put the ID in column D and the name in column A? MATCH searches D, INDEX fetches from A, and it just works:

' A = Name, D = ID.  You have the ID; you want the Name to its LEFT.
=INDEX(A2:A500, MATCH(F2, D2:D500, 0))    ' effortless — VLOOKUP can't do this

This is the headline advantage. VLOOKUP can only return columns to the right of the search column; INDEX/MATCH has no such rule because there is no single "table" with a fixed first column — just two ranges you name explicitly.

It survives inserted columns

VLOOKUP's col_index_num is a hard-coded integer that points at the wrong column the instant someone inserts one. INDEX/MATCH never counts columns — it points at named ranges or whole-column references that Excel updates automatically:

=INDEX(C2:C500, MATCH(F2, A2:A500, 0))
' Insert a column between A and C? Excel rewrites C2:C500 -> D2:D500 for you.

Insert, delete, or move a column and the references shift with it. This is why INDEX/MATCH is the safer choice for a workbook that other people will edit — the formula describes which data it wants, not how many columns over it happens to be today.

The match_type trap (the same lesson as VLOOKUP)

MATCH's third argument is exactly the footgun VLOOKUP's fourth is. It defaults to 1 (approximate, assumes ascending sort). You almost always want 0:

=MATCH(F2, A2:A500)       ' DANGER — defaults to 1, approximate, needs sorted data
=MATCH(F2, A2:A500, 1)    ' approximate: largest value <= F2 (ascending required)
=MATCH(F2, A2:A500, 0)    ' RIGHT for exact lookups — the row that equals F2
=MATCH(F2, A2:A500, -1)   ' approximate: smallest value >= F2 (descending required)

Type 0 for the exact match you almost certainly want. Approximate MATCH has the same legitimate niche as approximate VLOOKUP — banded lookups on a sorted column (find which bracket a value falls into) — and the same silent-wrong-answer failure everywhere else.

The move nothing else does cleanly: a two-way lookup

Here's where INDEX/MATCH stops being "VLOOKUP done better" and becomes its own tool. INDEX accepts both a row number and a column number. Feed each from its own MATCH and you look up a value at the intersection of a row key and a column key — a lookup against a matrix:

' Grid: months down column A, regions across row 1, numbers in the middle.
=INDEX(B2:M13, MATCH(F2, A2:A13, 0), MATCH(G2, B1:M1, 0))
'              ^which row (month)        ^which column (region)

One MATCH locates the row, the other locates the column, and INDEX returns the cell where they cross. VLOOKUP needs a clumsy helper column for this; XLOOKUP needs to be nested inside another XLOOKUP. INDEX with two MATCHes reads it in one line — still the cleanest two-way lookup in Excel.

Wrapping errors — tightly

A MATCH that finds nothing returns #N/A, which propagates out through INDEX. Handle it the same disciplined way: fix the cause first (spaces, text-vs-number mismatch), then wrap only once the formula is correct.

=IFERROR(INDEX(C2:C500, MATCH(F2, A2:A500, 0)), "Not found")

Prefer IFERROR here over a blanket wrapper that would also swallow a genuinely broken reference. #N/A from MATCH is information — it tells you the key isn't there.

The judgment call: INDEX/MATCH, VLOOKUP, or XLOOKUP?

  • XLOOKUP — if you're on Excel 365, it's the default for a one-value lookup: it looks either direction, takes a built-in not-found value, and reads more simply than nested INDEX/MATCH.
  • INDEX/MATCH — still the winner for two-way (row × column) lookups, and the most robust choice in older versions without XLOOKUP where you need left-lookups and insert-proof references.
  • VLOOKUP — fine for the simple exact-match-return-to-the-right case, but the one that breaks when columns move.

The skill is reading the shape of the problem: one value or an intersection? Modern Excel or an old shared workbook? That tells you which of the three you're holding.

How ExcelMaster helps

The hardest part of INDEX/MATCH for most people is the nesting — remembering which range MATCH searches, which one INDEX returns, and where the two MATCHes go in a two-way lookup. ExcelMaster writes the whole pattern from a plain description ("pull each order's region from the customer sheet, matching on customer ID"), locks in exact match, and picks the right structure — a single XLOOKUP when that's cleaner, a two-MATCH INDEX when you're really doing a grid lookup — so you're not hand-assembling brackets.

Frequently asked questions

What is INDEX MATCH and how does it work?

It's a lookup made of two functions. MATCH(value, lookup_range, 0) returns the position of a value in a column; INDEX(return_range, position) returns the value at that position. Combined as =INDEX(return_range, MATCH(value, lookup_range, 0)), MATCH finds the row and INDEX fetches the result from any column you choose.

Why use INDEX MATCH instead of VLOOKUP?

Three reasons: it can look left (return a column before the search column), it survives inserted or moved columns because it doesn't hard-code a column number, and it extends to a true two-way (row × column) lookup. VLOOKUP can only return columns to the right and breaks when the table's columns change.

How do I do a two-way lookup with INDEX and MATCH?

Use two MATCH functions inside INDEX, one for the row and one for the column: =INDEX(data, MATCH(row_key, row_headers, 0), MATCH(col_key, col_headers, 0)). The first MATCH finds which row, the second finds which column, and INDEX returns the value where they intersect.

What does the 0 in MATCH mean?

0 is the match_type for an exact match — the position of the value that equals your lookup value. 1 (the default) and -1 do approximate matching on sorted data. Use 0 for normal lookups; omitting it defaults to 1 and can silently return the wrong row on unsorted data.

Is INDEX MATCH still worth learning if I have XLOOKUP?

Yes. XLOOKUP replaces INDEX/MATCH for simple one-value lookups, but INDEX/MATCH is still the cleanest way to do a two-way (row × column) lookup, and it's essential in older Excel versions that don't have XLOOKUP.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-05.

Related guides: Excel VLOOKUP · Excel HLOOKUP & LOOKUP · XLOOKUP (2025) · Excel IFERROR · Excel FILTER