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.
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.
Every ✓ / ◐ / ✗ is a behavior we evaluated in HyperFormula 3.5 — not a marketing claim.
| Capability | VLOOKUP | INDEX MATCH | Why 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 independent | INDEX MATCH's headline advantage. |
| Survives column insertion | ✗ No — col_index is a fixed number | ✓ Yes — explicit ranges shift | VLOOKUP silently returns the wrong column. |
| Two-way (row + column) | ✗ No — static col_index | ✓ Yes — double MATCH | INDEX(match_row, match_col). |
| Approximate match | ✓ Yes — TRUE, ascending | ✓ Yes — MATCH(...,1), ascending | Same largest-value-≤-key algorithm. |
| Missing-key default | ◐ Wrap in IFERROR/IFNA | ◐ Wrap in IFERROR/IFNA | Both return #N/A raw. |
| Beginner readability | ✓ One function, 4 args | ◐ Two nested functions | VLOOKUP is quicker to write and read. |
| Speed (HyperFormula) | ✓ ~6.7s / 200 lookups @10k rows | ✓ ~6.9s / 200 lookups @10k rows | Essentially tied — see benchmark. |
VLOOKUP hardcodes a column number; INDEX/MATCH splits position (MATCH) from return (INDEX).
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.
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.
=VLOOKUP(103,A2:D7,3,FALSE)=INDEX(C2:C7,MATCH(103,A2:A7,0))=VLOOKUP(102,A2:C7,1,FALSE)=INDEX(A2:A7,MATCH(102,B2:B7,0))=VLOOKUP(999,A2:D7,3,FALSE)=INDEX(C2:C7,MATCH(999,A2:A7,0))=VLOOKUP(75,A2:B6,2,TRUE)=INDEX(B2:B6,MATCH(75,A2:A6,1))=VLOOKUP(102,A2:E7,3,FALSE)=INDEX(D2:D7,MATCH(102,A2:A7,0))=VLOOKUP("West",A2:E4,4,FALSE)=INDEX(B2:E4,MATCH("West",A2:A4,0),MATCH("Q3",B1:E1,0))=VLOOKUP("Widget",B2:D7,2,FALSE)=INDEX(C2:C7,MATCH("Widget",B2:B7,0))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 size | VLOOKUP | INDEX MATCH |
|---|---|---|
| 1,000 rows × 200 lookups | 195.49 ms | 169.38 ms |
| 5,000 rows × 200 lookups | 1854.51 ms | 1945.77 ms |
| 10,000 rows × 200 lookups | 6727.16 ms | 6897.27 ms |
Stable right-side table → VLOOKUP is fine; left lookups, shifting columns, or reuse → INDEX/MATCH.
| Situation | Use | Why |
|---|---|---|
| Return column is right of the key, table is stable | VLOOKUP | VLOOKUP's four-arg form is the quickest to write for a simple right-side lookup. |
| You must return a column left of the key | INDEX MATCH | VLOOKUP returns #N/A looking left; INDEX/MATCH returns any column. |
| Columns get inserted or reordered | INDEX MATCH | A hardcoded col_index breaks on insert; MATCH tracks the key by position. |
| One key feeds many return columns | INDEX MATCH | Compute MATCH once and reuse it across several INDEX calls. |
| Approximate match on a sorted tier table | Either | VLOOKUP TRUE and MATCH match_type 1 both walk ascending thresholds. |
| A two-way (row + column) lookup | INDEX MATCH | A double MATCH finds both coordinates; VLOOKUP needs a helper column. |
| The reader is new to spreadsheets | VLOOKUP | One named function is easier to grasp than two nested ones. |
| You want a modern single-function successor | Either | XLOOKUP supersedes both with left lookup and a built-in default — see the related page. |
Replace the column number with a MATCH position and an explicit return range.
| Task | VLOOKUP / old way | INDEX MATCH | What 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. |