Function comparison · engine-measured

VLOOKUP vs INDEX MATCH

One editable product table, two formulas side by side. VLOOKUP counts columns from the left of its table; INDEX/MATCH finds a position with MATCH and returns it with INDEX — so it can look left and survive column inserts. Every result below is evaluated live in HyperFormula 3.5.

fx
VLOOKUP(…)  vs  INDEX MATCH(…)
7
measured scenarios
#N/A
VLOOKUP can't look left
3 vs 4
args: INDEX/MATCH vs VLOOKUP
1990s
works in every Excel
▶ Try both live See the feature matrix
🏆Verdict: INDEX/MATCH for new work; VLOOKUP for a quick right-side lookup.

INDEX/MATCH separates position (MATCH) from return (INDEX), so it looks left, survives column inserts, and reuses one MATCH across many returns. VLOOKUP is one function with four arguments and is fine when the return column sits to the right of the key and the table is stable. Both return #N/A on a missing key and both support approximate match on sorted data — wrap either in IFNA. In our engine the two trade places on speed as rows grow, so neither is a clear performance winner.

VLOOKUP strengths

  • One function, four args — fastest to write
  • Approximate match for sorted tier tables
  • Universally understood by spreadsheet users
  • Fine when the return column is right of the key

INDEX MATCH strengths

  • Looks left — returns a column before the key
  • Survives column inserts (no hardcoded index)
  • Return range explicit; double MATCH does two-way
  • One MATCH can feed many INDEX returns

Feature-by-feature matrix

Every ✓ / ◐ / ✗ is a behavior we evaluated in HyperFormula 3.5 — not a marketing claim.

CapabilityVLOOKUPINDEX MATCHWhy it matters
Exact match✓ Yes — FALSE✓ Yes — MATCH(...,0)Identical results on a clean key.
Look up to the LEFT✗ No — key must be leftmost✓ Yes — return range is independentINDEX MATCH's headline advantage.
Survives column insertion✗ No — col_index is a fixed number✓ Yes — explicit ranges shiftVLOOKUP silently returns the wrong column.
Two-way (row + column)✗ No — static col_index✓ Yes — double MATCHINDEX(match_row, match_col).
Approximate match✓ Yes — TRUE, ascending✓ Yes — MATCH(...,1), ascendingSame largest-value-≤-key algorithm.
Missing-key default◐ Wrap in IFERROR/IFNA◐ Wrap in IFERROR/IFNABoth return #N/A raw.
Beginner readability✓ One function, 4 args◐ Two nested functionsVLOOKUP is quicker to write and read.
Speed (HyperFormula)✓ ~6.7s / 200 lookups @10k rows✓ ~6.9s / 200 lookups @10k rowsEssentially tied — see benchmark.

Syntax, side by side

VLOOKUP hardcodes a column number; INDEX/MATCH splits position (MATCH) from return (INDEX).

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_valueThe value to find in the LEFTMOST column of table_array.
table_arrayThe whole table range, e.g. A2:D100.
col_index_numA hard-coded column number to return (1 = the lookup column).
range_lookupFALSE = exact match; TRUE/omitted = approximate (needs ascending sort).
=VLOOKUP(103,A2:D7,3,FALSE)

INDEX MATCH

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
return_rangeThe column (or row) you want values FROM — independent of the key column.
lookup_valueThe value to find.
lookup_rangeThe column (or row) to search — can sit anywhere, left or right.
0MATCH's exact-match mode. Use 1 for approximate (ascending), -1 for descending.
=INDEX(C2:C7,MATCH(103,A2:A7,0))

Live head-to-head playground

Both formulas run on the SAME editable grid below (ID · Product · Price · Stock). Change a cell or type your own formula and watch the two results update live.

A1Sample data — click any cell to edit; both formulas re-evaluate live

VLOOKUP

—

INDEX MATCH

—
HyperFormula 3.5 · live

Every difference, measured

These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.

Exact match — the baseline tie

Look up the Price of ID 103.
VLOOKUP
=VLOOKUP(103,A2:D7,3,FALSE)
4.25
INDEX MATCH
=INDEX(C2:C7,MATCH(103,A2:A7,0))
4.25
Both return 4.25. On a clean, unique key with the return column to the right, VLOOKUP and INDEX MATCH are interchangeable.

Left lookup — VLOOKUP's hard wall

The key (ID) sits in column B; return the Product name from column A, to its LEFT.
VLOOKUP
=VLOOKUP(102,A2:C7,1,FALSE)
#N/A
INDEX MATCH
=INDEX(A2:A7,MATCH(102,B2:B7,0))
Gadget
VLOOKUP returns #N/A: it only searches the leftmost column of its range (here Product), so the numeric ID 102 is never found. INDEX MATCH searches column B explicitly and returns the Product from column A — to the LEFT of the key. This is the wall VLOOKUP cannot cross within one range.

Missing key — both fall the same way

Look up ID 999, which does not exist.
VLOOKUP
=VLOOKUP(999,A2:D7,3,FALSE)
#N/A
INDEX MATCH
=INDEX(C2:C7,MATCH(999,A2:A7,0))
#N/A
Both return #N/A. Neither has a built-in default; wrap either in IFERROR or IFNA to show 'Not found' instead of an error.

Approximate match on a sorted band table

Find the discount for Qty 75 (bands 0/10/50/100/500, ascending).
VLOOKUP
=VLOOKUP(75,A2:B6,2,TRUE)
0.1
INDEX MATCH
=INDEX(B2:B6,MATCH(75,A2:A6,1))
0.1
Both return 0.1 — the discount for the 50 band, the largest Qty ≤ 75. VLOOKUP TRUE and MATCH(...,1) run the same binary-style search and BOTH require the lookup column sorted ascending; unsorted data gives silently wrong answers.

A column was inserted — the silent breakage

A Category column was inserted before Price. The old VLOOKUP still says col_index 3; INDEX/MATCH points at the Price range explicitly.
VLOOKUP
=VLOOKUP(102,A2:E7,3,FALSE)
Electronics
INDEX MATCH
=INDEX(D2:D7,MATCH(102,A2:A7,0))
19.5
The measured breakage: after a Category column is inserted, VLOOKUP's hard-coded col_index 3 now points at Category and returns 'Electronics' — a plausible-looking WRONG answer with no error. INDEX MATCH still points at the explicit Price range D2:D7 and returns the correct 19.5. This is why maintainable models avoid hard-coded column numbers.

Two-way lookup — row AND column

Get West's Q3 sales from the matrix: the row is matched by region, the column by quarter header.
VLOOKUP
=VLOOKUP("West",A2:E4,4,FALSE)
110
INDEX MATCH
=INDEX(B2:E4,MATCH("West",A2:A4,0),MATCH("Q3",B1:E1,0))
110
Both reach 110 (West × Q3). But VLOOKUP hard-codes column 4; INDEX MATCH derives the column from MATCH("Q3",B1:E1,0). Change the quarter header you search for and only INDEX MATCH adapts — VLOOKUP would need its number edited by hand.

Duplicate keys — first match wins

Two rows are named Widget (ID 101 and 106). Return the Price of the first Widget.
VLOOKUP
=VLOOKUP("Widget",B2:D7,2,FALSE)
9.99
INDEX MATCH
=INDEX(C2:C7,MATCH("Widget",B2:B7,0))
9.99
Both return 9.99 — the FIRST Widget (ID 101), not the second (ID 106, price 11). Neither function warns about duplicate keys; exact-match VLOOKUP and MATCH(...,0) both stop at the first hit.

Engine benchmark

200 exact-match lookups per engine on tables of 1,000 / 5,000 / 10,000 rows, same data and keys. In HyperFormula 3.5 the two are essentially tied (6.7s vs 6.9s at 10,000 rows). Choose on capability, not speed. HyperFormula is NOT Excel — absolute numbers are engine-specific and only the relative signal is meaningful.

Table sizeVLOOKUPINDEX MATCH
1,000 rows × 200 lookups195.49 ms169.38 ms
5,000 rows × 200 lookups1854.51 ms1945.77 ms
10,000 rows × 200 lookups6727.16 ms6897.27 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

Stable right-side table → VLOOKUP is fine; left lookups, shifting columns, or reuse → INDEX/MATCH.

SituationUseWhy
Return column is right of the key, table is stableVLOOKUPVLOOKUP's four-arg form is the quickest to write for a simple right-side lookup.
You must return a column left of the keyINDEX MATCHVLOOKUP returns #N/A looking left; INDEX/MATCH returns any column.
Columns get inserted or reorderedINDEX MATCHA hardcoded col_index breaks on insert; MATCH tracks the key by position.
One key feeds many return columnsINDEX MATCHCompute MATCH once and reuse it across several INDEX calls.
Approximate match on a sorted tier tableEitherVLOOKUP TRUE and MATCH match_type 1 both walk ascending thresholds.
A two-way (row + column) lookupINDEX MATCHA double MATCH finds both coordinates; VLOOKUP needs a helper column.
The reader is new to spreadsheetsVLOOKUPOne named function is easier to grasp than two nested ones.
You want a modern single-function successorEitherXLOOKUP supersedes both with left lookup and a built-in default — see the related page.

VLOOKUP → INDEX/MATCH migration

Replace the column number with a MATCH position and an explicit return range.

TaskVLOOKUP / old wayINDEX MATCHWhat changed
Basic exact lookup=VLOOKUP(103,A2:D7,3,FALSE)=INDEX(C2:C7,MATCH(103,A2:A7,0))MATCH finds the row; INDEX returns it — no column number.
Left lookup=VLOOKUP("Gadget",B2:D7,1,FALSE)=INDEX(A2:A7,MATCH("Gadget",B2:B7,0))VLOOKUP cannot look left (#N/A); INDEX/MATCH returns the ID.
Missing key=VLOOKUP(999,A2:D7,3,FALSE)=INDEX(C2:C7,MATCH(999,A2:A7,0))Both return #N/A; wrap either in IFNA.
Approximate tier=VLOOKUP(103.5,A2:D7,3,TRUE)=INDEX(C2:C7,MATCH(103.5,A2:A7,1))MATCH match_type 1 = ascending approximate, same as VLOOKUP TRUE.
Guard the #N/A=IFNA(VLOOKUP(999,A2:D7,3,FALSE),"missing")=IFNA(INDEX(C2:C7,MATCH(999,A2:A7,0)),"missing")IFNA catches the not-found error on either side.

Frequently asked questions

Is INDEX MATCH faster than VLOOKUP?
In our HyperFormula benchmark they are essentially tied (6.7s vs 6.9s for 200 lookups across 10,000 rows). In desktop Excel, INDEX MATCH with an approximate MATCH on sorted data can be faster on very large tables, but for ordinary exact-match lookups the difference is negligible. Decide on capability — left lookup, insertion safety, two-way — not speed.
Can VLOOKUP look to the left?
Not within a single range. VLOOKUP always searches the leftmost column of table_array and can only return columns at or to the right of it. Workarounds are to start the range at the key column (still cannot go left of it), duplicate the key column to the left, or switch to INDEX MATCH / XLOOKUP, which separate the search range from the return range.
Why does VLOOKUP break when I insert a column?
col_index_num is a hard-coded number. Inserting a column shifts the data but not the number, so VLOOKUP silently returns a different column — our 'insert' scenario measured it returning 'Electronics' instead of the price 19.5, with no error. INDEX MATCH references explicit ranges that move with the data, so it stays correct.
Should I still learn VLOOKUP in 2026?
Yes — for reading the millions of legacy workbooks that use it, and for quick throwaway lookups where its brevity wins. For new models you will maintain, prefer INDEX MATCH (works in every Excel version) or XLOOKUP (Excel 2021+/365). Knowing all three lets you read anything and write the durable version.
VLOOKUP vs INDEX MATCH vs XLOOKUP — which wins?
XLOOKUP does everything INDEX MATCH does in one function, with a built-in not-found default and search-direction control, but needs Excel 2021+/365. INDEX MATCH is the portable equivalent that runs in Excel 2007 onward and Google Sheets. VLOOKUP is the simplest to write but the most fragile. See our VLOOKUP vs XLOOKUP and INDEX MATCH vs XLOOKUP comparisons.