Why use INDEX-MATCH instead of VLOOKUP?

It can look left (return a column before the key), it doesn't break when columns are inserted if you MATCH the header, and it runs in every Excel version and Google Sheets. It's the most portable lookup pattern.

How do I do a left lookup with INDEX-MATCH?

Point INDEX at the column you want (even left of the key) and MATCH down the key column: =INDEX(A2:A9, MATCH(H2, C2:C9, 0)). The return column's position doesn't matter.

What does the 0 in MATCH do?

It forces exact match on unsorted data. Omitting it defaults to 1 (approximate, ascending), which silently returns wrong positions on unsorted lists — the most common INDEX-MATCH bug.

Is INDEX-MATCH faster than VLOOKUP?

In our 100,000-row exact-match, full-column benchmark it was the slowest of the three (VLOOKUP fastest, XLOOKUP middle). Its advantage is portability and left-lookups, not raw speed on big exact tables.

How do I do a two-way (matrix) lookup?

Use two MATCHes: one down the first column for the row, one across the header for the column: =INDEX(B2:D9, MATCH(H2,A2:A9,0), MATCH(H3,B1:D1,0)).

INDEX-MATCH — Syntax, Left Lookups, 14 Error Fixes & Universal Compatibility

fx INDEX

=INDEX(array, row_num, [column_num])

arrayrequired

The range to return a value from — any column, left or right of the key.

row_numrequired

Position within array; supply MATCH here.

column_numoptional

Column within array; a second MATCH makes it insert-safe.

fx MATCH

=MATCH(lookup_value, lookup_array, [match_type])

lookup_valuerequired

The value to find.

lookup_arrayrequired

A single row or column to search.

match_typeoptional

Use 0 for exact (default is 1 = approximate, the classic trap).

// 02 — Original data INDEX-MATCH vs VLOOKUP vs XLOOKUP

We benchmarked exact-match lookups in HyperFormula 3.3.1 — median recalculation time across four table sizes. This is original measured data, not republished.

VLOOKUP XLOOKUP INDEX-MATCH

Table rows	VLOOKUP (ms)	XLOOKUP (ms)	INDEX-MATCH (ms)

How we measured · who made this

LookupLab Research · run Nov 2026 · Apple Silicon · HyperFormula 3.3.1 · median of repeated runs

Exact-match lookups on a sorted table, full-column reference, median recalculation time across four table sizes. Every result was correctness-verified before timing. This is directly relevant to web spreadsheet tools and indicative, not identical to native Excel. In our exact-match, full-column benchmark INDEX-MATCH was the slowest of the three — its win is portability and left-lookups, not raw speed. Choose it for shared or legacy workbooks where XLOOKUP isn't available; choose VLOOKUP for pure speed on big exact tables.

⬇ Reproduction script (bench.mjs) ⬇ Raw data (bench-hf.json)

Text drafted with AI assistance and edited by LookupLab. Every benchmark number is machine-measured and reproducible, not generated. Data licensed CC BY 4.0.

// 03 — Try it live

Interactive lookup playground

Edit the cells, change the lookup key, switch functions and match modes. The result and the generated formula update instantly.

A1Employee data. Click any cell to edit

Lookup key (ID) 1003

Return column

Function

Match mode

Table rows 8

Grid theme

Highlight match

IFERROR wrap

Fallback value

Result

$62,000

=VLOOKUP(1003,A2:D9,4,0)

✓ foundinstant

// 04 — Error library

The real-world error library

14 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

🔍

// 05 — Cross-platform

Where VLOOKUP works

Availability and syntax differences across desktop Excel, Google Sheets, LibreOffice and the web spreadsheet engines — including the exact-match quirk we found in our benchmark.

Platform	Support	Notes	Gotcha

✓ Supported full VLOOKUP◐ Partial works with caveatsCompiled from public docs · verify against your build

// 06 — Go deeper

🔒

Unlock the full VLOOKUP course

Everything above is free. Go Pro for the deep end.

✓ 200 graded practice problems with instant feedback ✓ INDEX-MATCH vs VLOOKUP deep dive + when to switch ✓ Two-way & leftward lookup patterns ✓ Downloadable workbook + completion certificate

$9/mo $59/yr · save 45% $149 lifetime

Look left, look anywhere —
INDEX-MATCH runs everywhere.

Interactive lookup playground

The real-world error library

Where VLOOKUP works

Unlock the full VLOOKUP course

Frequently asked

Interactive lookup playground

The real-world error library

Where VLOOKUP works

Unlock the full VLOOKUP course

Frequently asked

Related functions