The INDEX + MATCH pattern, explained interactively.
INDEX + MATCH is two functions composed into the most flexible lookup pattern in spreadsheets. MATCH finds the row number of your lookup value; INDEX plucks the value at that row from any column — left or right of the lookup column, VLOOKUP be damned.
How to use INDEX + MATCH
- Start from the inside out. Write the MATCH call first:
=MATCH(lookup_value, lookup_column, 0). It returns a 1-based row position. - Wrap that in INDEX:
=INDEX(return_column, MATCH(...)). INDEX uses the integer MATCH hands it to pluck the value at that row. - Always pass
0as MATCH’s third argument for an exact match. Omitting it defaults to approximate match, which silently misbehaves on unsorted data. - To look up a value and a column by header name, nest two MATCHes:
=INDEX(table, MATCH(row, rows, 0), MATCH(col, cols, 0)).
Try the demo — change the SKU or the return column and watch MATCH find the row, then INDEX pluck the price.
0 for an exact match — anything else gives you an approximate match that usually misbehaves unsorted data.| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Name | Price | Result |
| 2 | A-101 | Pencil | 1.20 | 12.40 |
| 3 | A-102 | Notebook | 3.80 | |
| 4 | B-210 | Stapler | 12.40 | |
| 5 | C-305 | Whiteboard | 48.00 | |
| 6 | C-306 | Marker set | 8.50 | |
| 7 | D-410 | Sticky pad | 2.75 |
3, INDEX returns 12.40.INDEX + MATCH syntax and arguments
Two functions, composed from the inside out. See Microsoft’s official INDEX reference and MATCH reference for canonical specifications.
lookup_column for this value and returns its row position.return_column — they’re aligned by row index, so mismatched lengths silently return wrong values.0 for exact match. 1 (the default) does an approximate match on sorted data; -1 does descending approximate match. For the canonical lookup use case, only 0 is safe.INDEX + MATCH examples
Four patterns that cover almost every INDEX + MATCH you’ll ever need.
Example 1: INDEX + MATCH for a right-to-left lookup
The canonical reason to reach for INDEX + MATCH — the lookup column is to the right of the return column.
A holds IDs, B holds names; you know the name (in E1) and need the ID. VLOOKUP can’t do this without restructuring the table. INDEX + MATCH doesn’t care.
Example 2: Two-way INDEX + MATCH
Lookup intersects a row and a column — e.g. find revenue for a specific region in a specific month.
INDEX’s 3-argument form takes a row offset and a column offset. This is where INDEX + MATCH genuinely outshines VLOOKUP — VLOOKUP can only return from a fixed column index.
Example 3: INDEX + MATCH survives column insertions
VLOOKUP uses an integer column index. If someone inserts a column, the index shifts and the formula returns wrong data — silently. INDEX + MATCH references columns by range, so it survives.
Using structured table references (prices[Price]), the formula is immune to column shuffles in the prices table. Same applies with named ranges.
Example 4: INDEX + MATCH wrapped in IFNA
Catch the #N/A that MATCH produces when the lookup value is missing.
Prefer IFNA over IFERROR around this pattern. IFERROR would also catch #REF! or #NAME? and mask genuine bugs; IFNA only catches the missing-match case.
Common INDEX + MATCH errors and fixes
Four failure modes, each with what to check and how to recover.
INDEX + MATCH returns #N/A
Cause: MATCH can’t find the lookup value. Either it truly isn’t there, the cell has trailing whitespace, or the value is stored as text vs a number.
Run TRIM(A2)=TRIM(E1) on a spare cell to test for whitespace; run ISNUMBER(A2) to check type. Wrap with IFNA for a safe fallback.
INDEX + MATCH wrong value, no error
Cause: you forgot to pass 0 as MATCH’s third argument. The default approximate match returns a wrong row on unsorted data — silently.
Always include the explicit , 0) at the end of MATCH. Make it a habit even on sorted data — a later sort change shouldn’t break the formula.
INDEX + MATCH returns #REF!
Cause: MATCH returned a row number outside INDEX’s return_column bounds. Usually because the two ranges have different heights.
Align the ranges — return_column and lookup_column must be the same height. Full- column refs (A:A, B:B) avoid the issue.
INDEX + MATCH broken after a paste
Cause: paste operations sometimes convert range references to absolute ($A$2), breaking fill-down.
Use F4 to toggle reference locking. For the lookup column you typically want absolute; for the row index you usually want relative. Structured table references sidestep this entirely.
INDEX + MATCH vs VLOOKUP, XLOOKUP & HLOOKUP
Four lookup strategies. Pick based on direction, Excel version support, and how resilient the formula needs to be to column changes.
| Function | Lookup direction | Survives column insertion | Available in |
|---|---|---|---|
| INDEX + MATCH | Any direction, including 2D | Yes — references by range | Every Excel version |
| VLOOKUP | Left-to-right only | No — col_index_num is a fixed integer | Every Excel version |
| XLOOKUP | Any direction, single cell | Yes — references by range | Excel 2021+, Sheets |
| HLOOKUP | Top-to-bottom only | No — row-based integer index | Every Excel version |
Rule of thumb: if you ship to Excel 2021 or newer, reach for XLOOKUP first — it handles every case INDEX + MATCH does with a cleaner signature. Stay with INDEX + MATCH when you need 2D lookups (row + column) or must support older Excel. Use VLOOKUP only when the lookup column is already leftmost and you can’t introduce newer functions.
INDEX + MATCH frequently asked questions
6.01Why use INDEX + MATCH instead of VLOOKUP?▸
INDEX + MATCH looks both ways — VLOOKUP requires the lookup column to be left of the return column. INDEX + MATCH also survives column insertions, since it references columns by name rather than by the VLOOKUP col_index_num integer.
6.02Is XLOOKUP a replacement for INDEX + MATCH?▸
For most cases, yes — XLOOKUP combines MATCH’s flexibility with VLOOKUP’s readability in one function. INDEX + MATCH still wins on 2D lookups (row and column) and on compatibility with older Excel versions where XLOOKUP is unavailable.
6.03Why do I need the 0 at the end of MATCH?▸
The third argument of MATCH is match_type. 0 means exact match. 1 (the default) means approximate match on sorted data, which quietly returns the wrong row on unsorted data — almost always a bug. Always pass 0 for the lookup use case.
6.04Can INDEX + MATCH do a two-way lookup?▸
Yes — nest two MATCHes, one for the row and one for the column: =INDEX(table, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0)). This is INDEX + MATCH’s killer feature over VLOOKUP.
6.05Why does INDEX + MATCH return #N/A?▸
MATCH couldn’t find the lookup value in the lookup column. Either the value doesn’t exist, the comparison is tripping on whitespace or text-vs-number, ormatch_type isn’t 0. Wrap the whole formula in IFNA for a clean fallback.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.