VLOOKUP vs INDEX MATCH: which should I use?

This comparison assumes the file must open in Excel 2019 or earlier, where XLOOKUP is not available. Keep VLOOKUP with FALSE when you need one field from a table whose first column is already the key, and the workbook is full of working VLOOKUPs. Use INDEX MATCH when one ID has to fill several cells, or when recalc is slow because the same lookup is copied across many columns. VLOOKUP vs INDEX MATCH, by job: - One rightward return, Excel 2010–2019, team already uses VLOOKUP: keep VLOOKUP with FALSE - Several returns from the same ID: one MATCH helper, then INDEX - Thousands of formula rows that all hit the same key column: INDEX MATCH for fewer scans - Microsoft 365 / Excel 2021: this is the wrong page—use XLOOKUP vs INDEX MATCH Do not rewrite a stable VLOOKUP-only close file just to look clever.

Is INDEX MATCH better than VLOOKUP?

Only for some jobs. INDEX MATCH is not newer magic; both functions run on Excel 2010 through 2019. INDEX MATCH wins when you need one search to feed many return cells, or when a counted col_index_num would break if someone inserts a column. VLOOKUP wins when the lookup is a single field to the right of the key and everyone who maintains the file already reads VLOOKUP. When INDEX MATCH is better: - One MATCH position reused across cost, buyer, terms, and on-hand - Recalc time matters on a large close workbook - You want the return column named, not numbered When VLOOKUP is better: - One exact return, key already in the leftmost column of table_array - A shared file full of working VLOOKUPs If both run, pick the one the next person on the file can edit.

Why is INDEX MATCH faster than VLOOKUP?

On a few hundred rows you will not feel it. The gap is how many times Excel walks the key column. Four VLOOKUPs on the same vendor ID each scan the lookup column—and often a wide table_array. One MATCH helper scans once; INDEX then picks cells by position. That structure is what people mean by "INDEX MATCH is faster"—not a secret engine inside MATCH. Which is faster, VLOOKUP or INDEX MATCH: - One return, small table: a tie - Four returns per row, 2,000 rows: MATCH once plus four INDEX beats four VLOOKUPs - Wide table_array (A:Z) in every VLOOKUP: extra columns travel with each scan - Approximate VLOOKUP on a sorted column: can be fast, but it is a different job than exact MATCH 0 Optimize the number of searches, not the function brand.

Does INDEX MATCH work in Excel 2010, 2016, and 2019?

Yes. INDEX and MATCH have been in Excel for decades. So has VLOOKUP. That is why this page exists: on those builds you cannot fall back to XLOOKUP. Open an XLOOKUP file in Excel 2019 and you get #NAME?. VLOOKUP vs INDEX MATCH is the live choice for shared 2010–2019 workbooks. Where both still run: - Excel 2010, 2013, 2016, and 2019 desktop - Many older Excel for Mac builds - Google Sheets, including files exported to old Excel Where XLOOKUP does not run: - Excel 2019 and earlier - Any recipient whose build you do not control Write MATCH's third argument as 0. Omit it and you get an approximate match—the same class of bug as omitting FALSE on VLOOKUP.

VLOOKUP vs INDEX MATCH in Google Sheets

Sheets has both, and current Sheets also has XLOOKUP. For a Sheets-only file that will never open in old Excel, XLOOKUP is usually clearer. For a Sheets file that finance still downloads into Excel 2016, stay on VLOOKUP or INDEX MATCH. INDEX MATCH is the better Sheets-to-old-Excel pattern when one key fills several cells. VLOOKUP vs INDEX MATCH in Google Sheets: - Sheets-only, one return, key on the left: VLOOKUP with FALSE is fine - Several returns from the same key: MATCH once, INDEX many times - File must open in Excel 2016/2019: do not use XLOOKUP - Missing keys: wrap IFNA; Sheets has no if_not_found on VLOOKUP Do not assume a Sheets XLOOKUP will calculate after a desktop Excel export.

Learn · lookup

VLOOKUP vs INDEX MATCH: which one should you use?

Updated 2026-08-21

INDEX MATCH vs VLOOKUP is the live choice when the workbook must still open in Excel 2019 or earlier—XLOOKUP is not on the table. Keep VLOOKUP with FALSE for one rightward return in a file full of working VLOOKUPs. Switch to INDEX MATCH when one ID has to fill several cells, or when recalc is dragging because the same lookup is copied across four columns. If you are on Microsoft 365, read XLOOKUP vs INDEX MATCH instead.

Pick the function from the file, not the blog post

Situation	Use
Excel 2010–2019, one return to the right of the key, team already uses VLOOKUP	VLOOKUP with FALSE
Same old file, several fields from one ID	One MATCH helper, then INDEX
Close workbook with thousands of repeated lookups	INDEX MATCH (fewer scans of the key)
Microsoft 365 / Excel 2021 / current Sheets	Wrong page—start at XLOOKUP vs INDEX MATCH

Left lookups and last-match searches are covered on the XLOOKUP comparison, not here. This page is old-file compatibility plus how many times Excel walks the key column.

Same lookup, two formulas

Vendor list on the sheet. Lookup ID in G2. When it is V-410, both formulas should return 8.25.

A (Vendor)	B (Unit cost)	C (Buyer)
V-410	8.25	Patel
V-882	14.00	Ng

VLOOKUP (write FALSE or you get an approximate match):

=VLOOKUP(G2, A2:C5000, 2, FALSE)

INDEX MATCH (write 0 for the same exact match):

=INDEX(B2:B5000, MATCH(G2, A2:A5000, 0))

Sample check: put V-000 in G2. Both return #N/A unless you wrap IFNA. Omit VLOOKUP’s FALSE or MATCH’s 0 and you are no longer doing the same job.

What actually changes (VLOOKUP vs INDEX MATCH)

Behavior	VLOOKUP	INDEX MATCH
Compatibility	Excel 2010–2019, Sheets—no #NAME? on old desktop	Same. Both beat XLOOKUP on old files
Searches per key	One scan per VLOOKUP; four fields = four scans	One MATCH can feed many INDEX formulas
Return column	Counted col_index_num	You point at the return range
Default match	Approximate if FALSE is omitted	MATCH approximates if you omit match_type—write 0
table_array width	Extra columns in A:Z ride along on every call	MATCH only walks the key column
Excel vs Sheets	Same FALSE/0 exact-match contract	Same; export to old Excel still calculates

The dangerous default is shared: omit FALSE or 0 and Excel nearest-matches a list that is often unsorted. That is a setup bug, not a reason to pick one function over the other.

Example 1 — one return in an Excel 2016 file (VLOOKUP wins)

Shared close file. Recipients are on Excel 2016. You need unit cost only. The team already maintains VLOOKUPs. Rewriting this to INDEX MATCH adds syntax with no compatibility gain and no extra fields to reuse.

=IFNA(VLOOKUP(G2, A2:C5000, 2, FALSE), "")

INDEX MATCH does the same job:

=IFNA(INDEX(B2:B5000, MATCH(G2, A2:A5000, 0)), "")

Sample check: G2=V-410 returns 8.25 on both. The win is readability on a VLOOKUP-only desk, not a different result. Leave a stable one-column lookup alone.

Example 2 — one MATCH, four INDEX (INDEX MATCH wins)

Same Excel 2016 vendor file. The dashboard needs unit cost, buyer, terms, and on-hand from V-410. Four VLOOKUPs each walk column A. One MATCH walks it once.

A (Vendor)	B (Cost)	C (Buyer)	D (Terms)	E (On-hand)
V-410	8.25	Patel	Net 30	120
V-882	14.00	Ng	Net 45	35

Four VLOOKUPs (four scans):

=VLOOKUP($G2, $A$2:$E$5000, 2, FALSE)
=VLOOKUP($G2, $A$2:$E$5000, 3, FALSE)
=VLOOKUP($G2, $A$2:$E$5000, 4, FALSE)
=VLOOKUP($G2, $A$2:$E$5000, 5, FALSE)

MATCH in J2, then INDEX (one scan):

=MATCH($G2, $A$2:$A$5000, 0)
=INDEX($B$2:$B$5000, $J2)
=INDEX($C$2:$C$5000, $J2)
=INDEX($D$2:$D$5000, $J2)
=INDEX($E$2:$E$5000, $J2)

Sample check: G2=V-410 → 8.25 / Patel / Net 30 / 120. This is not a 365 spill trick and not a left lookup. It is the reason INDEX MATCH still earns its keep on old files: one position, many returns.

Example 3 — 2,000-row close file (INDEX MATCH wins on structure)

Monthly close on Excel 2016: 2,000 vendor rows, four fields each. Same formulas as example 2, copied down. The result grid is what Excel actually does:

Setup	Formulas	Key-column scans
Four VLOOKUPs per row	8,000	8,000 (wide table_array each time)
One MATCH + four INDEX	10,000 cells, 2,000 searches	2,000 (INDEX does not re-search)

INDEX cells outnumber VLOOKUP cells and still recalc faster, because MATCH did the expensive walk. Pulling A:Z into every VLOOKUP makes this worse: extra columns ride along on each of the 8,000 calls. Sample check after a rewrite: pick three known vendors, confirm all four fields, then time a full recalc once—not a feeling.

Switching without creating new bugs

Keep VLOOKUP FALSE and MATCH 0. Do not mix those tokens into the other function.
Store MATCH in a helper column (or a named cell) and point every INDEX at that same row number. Do not nest a fresh MATCH inside each INDEX or you throw the speed win away.
Shrink ranges: MATCH walks A:A or A2:A5000, not A:Z. Prefer IFNA over IFERROR, which also hides #REF!.
After paste, test a known hit, a known miss, and one numeric-as-text ID. Then recalc the sheet once on the real row count.

Generate the formula, then run the sample checks

If the layout is clear but the argument order is not, describe the lookup cell, table, and return columns in plain English. Use Excel dialect for desktop 2016/2019 files, or Sheets dialect when the file lives in Google Sheets.

One-column VLOOKUP: VLOOKUP formula generator. Need INDEX MATCH for a multi-column old workbook? Excel formula generator. Always verify against a row you already know is correct before filling a whole column.

VLOOKUP vs INDEX MATCH FAQs

VLOOKUP vs INDEX MATCH: which should I use?: This comparison assumes the file must open in Excel 2019 or earlier, where XLOOKUP is not available. Keep VLOOKUP with FALSE when you need one field from a table whose first column is already the key, and the workbook is full of working VLOOKUPs. Use INDEX MATCH when one ID has to fill several cells, or when recalc is slow because the same lookup is copied across many columns. VLOOKUP vs INDEX MATCH, by job: - One rightward return, Excel 2010–2019, team already uses VLOOKUP: keep VLOOKUP with FALSE - Several returns from the same ID: one MATCH helper, then INDEX - Thousands of formula rows that all hit the same key column: INDEX MATCH for fewer scans - Microsoft 365 / Excel 2021: this is the wrong page—use XLOOKUP vs INDEX MATCH Do not rewrite a stable VLOOKUP-only close file just to look clever.
Is INDEX MATCH better than VLOOKUP?: Only for some jobs. INDEX MATCH is not newer magic; both functions run on Excel 2010 through 2019. INDEX MATCH wins when you need one search to feed many return cells, or when a counted col_index_num would break if someone inserts a column. VLOOKUP wins when the lookup is a single field to the right of the key and everyone who maintains the file already reads VLOOKUP. When INDEX MATCH is better: - One MATCH position reused across cost, buyer, terms, and on-hand - Recalc time matters on a large close workbook - You want the return column named, not numbered When VLOOKUP is better: - One exact return, key already in the leftmost column of table_array - A shared file full of working VLOOKUPs If both run, pick the one the next person on the file can edit.
Why is INDEX MATCH faster than VLOOKUP?: On a few hundred rows you will not feel it. The gap is how many times Excel walks the key column. Four VLOOKUPs on the same vendor ID each scan the lookup column—and often a wide table_array. One MATCH helper scans once; INDEX then picks cells by position. That structure is what people mean by "INDEX MATCH is faster"—not a secret engine inside MATCH. Which is faster, VLOOKUP or INDEX MATCH: - One return, small table: a tie - Four returns per row, 2,000 rows: MATCH once plus four INDEX beats four VLOOKUPs - Wide table_array (A:Z) in every VLOOKUP: extra columns travel with each scan - Approximate VLOOKUP on a sorted column: can be fast, but it is a different job than exact MATCH 0 Optimize the number of searches, not the function brand.
Does INDEX MATCH work in Excel 2010, 2016, and 2019?: Yes. INDEX and MATCH have been in Excel for decades. So has VLOOKUP. That is why this page exists: on those builds you cannot fall back to XLOOKUP. Open an XLOOKUP file in Excel 2019 and you get #NAME?. VLOOKUP vs INDEX MATCH is the live choice for shared 2010–2019 workbooks. Where both still run: - Excel 2010, 2013, 2016, and 2019 desktop - Many older Excel for Mac builds - Google Sheets, including files exported to old Excel Where XLOOKUP does not run: - Excel 2019 and earlier - Any recipient whose build you do not control Write MATCH's third argument as 0. Omit it and you get an approximate match—the same class of bug as omitting FALSE on VLOOKUP.
VLOOKUP vs INDEX MATCH in Google Sheets: Sheets has both, and current Sheets also has XLOOKUP. For a Sheets-only file that will never open in old Excel, XLOOKUP is usually clearer. For a Sheets file that finance still downloads into Excel 2016, stay on VLOOKUP or INDEX MATCH. INDEX MATCH is the better Sheets-to-old-Excel pattern when one key fills several cells. VLOOKUP vs INDEX MATCH in Google Sheets: - Sheets-only, one return, key on the left: VLOOKUP with FALSE is fine - Several returns from the same key: MATCH once, INDEX many times - File must open in Excel 2016/2019: do not use XLOOKUP - Missing keys: wrap IFNA; Sheets has no if_not_found on VLOOKUP Do not assume a Sheets XLOOKUP will calculate after a desktop Excel export.