Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
INDEX MATCH vs VLOOKUP: which is better in Excel and Sheets
Topic:VLOOKUPExcel & Google Sheets
=INDEX(C2:C4,MATCH(A2,B2:B4,0))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Find | ID | Name |
| B02 | B01 | Alice |
| B02 | Bob | |
| B03 | Carol |
=INDEX(C2:C4,MATCH(A2,B2:B4,0))→Bob
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1VLOOKUP syntax: =VLOOKUP(lookup_value, table_array, col_index_num, 0) — the lookup column MUST be the leftmost column of table_array.
- 2INDEX MATCH syntax: =INDEX(return_range,MATCH(lookup_value,lookup_range,0)) — the lookup column and return column are specified independently, so they can be in any order.
- 3Use VLOOKUP when your lookup column is always the first column, the table is simple, and you don't insert columns often.
- 4Use INDEX MATCH when you need to look left (return a column to the left of the lookup), want column insertions not to break the formula, or need better performance on large datasets.
Tips
- In Excel 365 and Google Sheets, XLOOKUP is even better than both — it looks in any direction, handles missing values with a custom default, and doesn't require the table to be sorted.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why does VLOOKUP break when I insert a column?
VLOOKUP uses a hard-coded column number (e.g., 3) which becomes stale when columns are inserted — column 3 is now what was column 4. INDEX MATCH references the return column directly, so it's immune to insertions.
Is INDEX MATCH slower than VLOOKUP on large datasets?
Historically VLOOKUP was slightly faster, but in modern Excel and Google Sheets the difference is negligible. INDEX MATCH's flexibility far outweighs any theoretical speed advantage.
More on VLOOKUP
See all →Reference
VLOOKUP
Look up a value in the first column of a range and return a value from another column in the same row.
How-toHow to VLOOKUP from another sheet
Pull a value from a different tab with VLOOKUP by prefixing the range with the sheet name. Works in Excel and Google Sheets.
Error fixFix #N/A
Fix #N/A errors in VLOOKUP in Excel and Google Sheets — covers missing matches, extra spaces, data-type mismatches, and range mistakes.
How-toHow to VLOOKUP with multiple criteria
Look up a value that matches two or more conditions using INDEX/MATCH with joined keys. Works in Excel 365 and Google Sheets.
Error fixFix Wrong value
VLOOKUP returns an incorrect value in Excel and Google Sheets — fix wrong column index, approximate match mode, unsorted data, and duplicate lookup keys.
How-toHow to fix VLOOKUP returning #N/A
VLOOKUP shows #N/A when it can’t find a match. Here are the real causes — exact-match, spaces, text-vs-number — and how to fix each.