VLOOKUP is the first lookup function most Excel users learn, and for a one-shot price lookup on a small sheet it’s perfectly fine. The trouble starts when the workbook grows: someone inserts a column upstream of yours and every formula points at the wrong field overnight, the lookup needs to read a value to the left of the key column, or the sheet hits a hundred thousand rows and recalculation stalls. INDEX MATCH solves all three problems with a slightly longer formula and a cleaner mental model. This guide shows when each function wins, walks through a working example on a spreadsheet grid, and ends with two patterns — two-way lookups and case-sensitive matching — that INDEX MATCH does almost effortlessly while VLOOKUP cannot.
VLOOKUP’s blind spots
VLOOKUP’s signature reveals its constraints. =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) looks up lookup_value in the first column of table_array and returns the value from the column whose hard-coded position is col_index_num. Three assumptions are baked in.
First, the key column must be the leftmost column of the table. If your data has employee IDs in column C and you want to read names from column B, VLOOKUP can’t help — you would have to move B physically to the right of C. Second, col_index_num is a static number, not a reference. The instant a coworker inserts a column in the middle of the table, col_index_num=4 stops pointing at the field you meant. Third, the default range_lookup value is approximate match, which silently returns wrong answers on unsorted data. Most production VLOOKUP bugs are one of those three.
A quick anatomy of the VLOOKUP arguments
If you’ve used VLOOKUP for years and never had a problem, your data is almost certainly small and your column order never changes. Both assumptions break the moment more than one person edits the workbook. The next section’s INDEX MATCH formula has the same goal as VLOOKUP but does not rely on either assumption.
How INDEX MATCH solves the same problem
INDEX and MATCH are two simple functions that do one job each. MATCH(lookup_value, lookup_range, 0) returns the row position of the lookup value inside lookup_range. INDEX(return_range, row_position) returns the value at that row position inside return_range. Compose them and you get a lookup that reads the key column and the return column independently:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))Because return_range and lookup_range are independent references, the lookup direction can go left, right, or skip columns entirely. Because they are references, inserting a column anywhere shifts them along automatically — the formula keeps working. The 0 as MATCH’s third argument forces exact match, which is what you almost always want.
Why the two functions work better together than alone
INDEX alone is useful when you already know the row number. MATCH alone is useful when you only need a position. The combination is what replaces VLOOKUP: MATCH supplies the position, INDEX retrieves the value, and neither function cares about column order. INDEX MATCH is also faster on large sheets because Excel processes only two narrow ranges instead of scanning the full table_array width that VLOOKUP needs.
Your first INDEX MATCH — a worked example
Put the following table on a sheet and use it to follow the formulas in this section. Column letters and row numbers are shown as a spreadsheet grid would render them so you can copy cell references exactly.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock |
| 2 | SKU-101 | Notebook | $4.20 | 312 |
| 3 | SKU-102 | Mechanical pencil | $2.80 | 540 |
| 4 | SKU-103 | Folder | $1.15 | 88 |
To find the price of SKU-103 using VLOOKUP, you would write =VLOOKUP("SKU-103", A2:D4, 3, 0). The 3 refers to the Price column’s position inside the table. If someone inserts a new column between B and C, that hard-coded 3 now points at the wrong field.
The INDEX MATCH equivalent reads the Price column directly:
=INDEX(C2:C4, MATCH("SKU-103", A2:A4, 0))MATCH returns 3 (the third row in A2:A4 holds SKU-103). INDEX returns the value in the third row of C2:C4, which is $1.15. Insert columns, rename the sheet, swap the order of B and C — the formula still resolves correctly because both ranges are named by their actual cells, not by an offset.
Reading left works the same way. To return the SKU when given a product name, swap the two ranges:
=INDEX(A2:A4, MATCH("Folder", B2:B4, 0))VLOOKUP cannot do this without rearranging the underlying table.
Locking down references for fill-down
When you copy the formula down a column, make sure your lookup and return ranges are absolute ($A$2:$A$4 rather than A2:A4) while the lookup_value stays relative. Without those locks, dragging the formula shifts every range and you end up looking at row 5 instead of row 2. INDEX MATCH inherits this from every Excel formula; it is the most common cause of “the formula gave the wrong answer when I copied it.” For a broader survey of similar workhorse formulas, see the essential Excel formulas guide.
Two-way lookups with INDEX MATCH MATCH
The pattern that genuinely separates INDEX MATCH from VLOOKUP is the two-way lookup: pick a row by one key and a column by another, and return the cell at their intersection. Quarterly sales by region is the canonical example. Set up a small grid:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Q4 |
| 2 | North | 12,400 | 15,180 | 14,020 | 17,650 |
| 3 | South | 9,860 | 11,200 | 10,940 | 13,510 |
| 4 | West | 14,700 | 16,090 | 15,330 | 18,820 |
To return the Q3 number for the West region, nest two MATCH calls — one for the row, one for the column — inside a single INDEX:
=INDEX(B2:E4, MATCH("West", A2:A4, 0), MATCH("Q3", B1:E1, 0))The first MATCH finds West in the region column and returns 3. The second MATCH finds Q3 in the header row and returns 3. INDEX reads row 3, column 3 of the value grid (B2:E4) and returns 15,330. Switch the lookup labels to any region/quarter combination and the same formula resolves correctly — that is the single biggest reason analysts pick INDEX MATCH over VLOOKUP for reporting grids. SUMIFS and COUNTIFS handle similar multi-criteria summarization but for aggregation rather than lookup.
When XLOOKUP changes the calculus
Excel 2019 (lifetime license) and Microsoft 365 added XLOOKUP, which folds the most useful INDEX MATCH features into a single function: it does left lookup natively, takes a return range directly, and supports an if_not_found argument so you no longer need to wrap with IFERROR. The same SKU price lookup becomes:
=XLOOKUP("SKU-103", A2:A4, C2:C4, "Not found")For a single-direction lookup, XLOOKUP is the cleanest of the three. INDEX MATCH still wins for two-way grids (XLOOKUP needs to be nested inside another XLOOKUP for the matrix case, which is verbose), and INDEX MATCH remains the only choice for workbooks that need to open in Excel 2016 or older builds. If you can guarantee everyone is on a modern Excel, see the XLOOKUP step-by-step guide for the full syntax. If you can’t, INDEX MATCH is the safer choice.
Common pitfalls and how to fix them
Most INDEX MATCH bugs trace to one of four causes. Walk them in order before assuming the data is wrong.
#N/A on a value you can see in the lookup column
The visible text doesn’t match the stored text. Leading or trailing spaces are the most common culprit — copy-paste from a PDF or web page often hides a non-breaking space at the end of a string. Wrap the MATCH argument with TRIM for a one-off fix, or run Find & Replace on the source column to clean it permanently. The second most common cause is a number stored as text. If your SKU column is text but the lookup value is a number (or vice versa), MATCH returns #N/A. Convert one side with VALUE() or TEXT().
The formula returns the wrong cell after I copy it
You forgot to lock the lookup and return ranges with absolute references. $A$2:$A$4 is locked; A2:A4 is not. When you drag the formula down, an unlocked range shifts row-by-row. Press F4 after typing the range to add the dollar signs.
I need case-sensitive matching
MATCH is not case-sensitive by default — "john" and "JOHN" return the same position. Combine MATCH with EXACT as an array operation to distinguish them:
=INDEX(C2:C100, MATCH(TRUE, EXACT(A2:A100, "JOHN"), 0))In Excel 365 the formula confirms with Enter; in older Excel versions enter it with Ctrl+Shift+Enter so Excel treats it as an array formula. EXACT returns TRUE only when the case matches exactly, and MATCH finds the first TRUE in the resulting array.
The lookup table grew but my formula didn’t notice
Hard-coded ranges (A2:A100) don’t expand when you append new rows. Either widen the ranges generously (A2:A10000) or — better — convert the source range to an Excel Table and reference structured columns: MATCH(lookup_value, tblSKUs[SKU], 0). Tables grow automatically, so the formula always covers the full current dataset.
Wrapping it up
INDEX MATCH isn’t faster to type than VLOOKUP, but it doesn’t break the way VLOOKUP breaks. You can read columns to the left, survive inserted columns, perform two-way lookups, and handle case-sensitive matching with the same two functions. XLOOKUP is the modern shortcut when your audience runs Excel 2019 or later — and it is the right choice for new workbooks meant to live on that stack. For everything else, INDEX MATCH remains the most portable lookup pattern in Excel, and the time you invest in learning it pays off the first time someone inserts a column in your data and your sheet doesn’t blink.