VLOOKUP vs XLOOKUP vs INDEX/MATCH: how to look up values in Excel
Spreadsheet tools · Published
Find a price, name or status using a code or ID, from the same sheet or another one, and choose the right lookup function for your Excel version.
The short answer
If you have Microsoft 365, Excel 2021, Excel 2024 or Excel for the web, use XLOOKUP: it does exact matches by default, can look left or right, and has a built-in "not found" message. In Excel 2019 or earlier, use VLOOKUP with FALSE as the last argument, or INDEX with MATCH when the value you want is to the left of the one you search for.
Whichever you pick, most lookup problems come from the data, not the formula: extra spaces, or numbers stored as text in one table and as numbers in the other.
The example data
The examples use a sheet called Prices with product codes in column A, names in column B and prices in column C. A second sheet, Orders, has the codes, and you want to fill in the price for each order.
| Row | A | B | C |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-100 | Stapler | 8.5 |
| 3 | P-101 | Desk tray | 12 |
| 4 | P-102 | Paper ream | 6.25 |
| 5 | P-103 | Label maker | 39 |
| 6 | P-104 | Whiteboard | 54 |
VLOOKUP (every version)
=VLOOKUP(B2,Prices!$A$2:$C$6,3,FALSE)- B2 is the value to find (the order's code).
- Prices!$A$2:$C$6 is the table on the Prices sheet. VLOOKUP always searches its first column. The dollar signs keep the range fixed when you copy the formula down.
- 3 is the column to return, counted from the left edge of the table: A is 1, B is 2, C is 3.
- FALSE asks for an exact match. Leave it out and VLOOKUP does an approximate match, which can return a wrong price without any error.
The main limit: VLOOKUP can't return a column to the left of the one it searches. And if someone inserts a column inside the table, the fixed 3 may now point at the wrong column.
INDEX and MATCH (every version)
=INDEX(Prices!$C$2:$C$6,MATCH(B2,Prices!$A$2:$A$6,0))MATCH finds the position of B2 in the code column (the 0 means exact match): P-103 is in position 4. INDEX then returns the 4th value from the price column. Because the search column and the return column are separate, the return column can be anywhere, including to the left. To find a code from a product name:
=INDEX(Prices!$A$2:$A$6,MATCH(G1,Prices!$B$2:$B$6,0))With "Label maker" in G1, this returns P-103.
XLOOKUP (Microsoft 365, Excel 2021 and later)
=XLOOKUP(B2,Prices!$A$2:$A$6,Prices!$C$2:$C$6,"Not found")The arguments are: the value to find, the column to search, the column to return, and what to show if there's no match. It's an exact match unless you ask otherwise. Microsoft says XLOOKUP isn't available in Excel 2016 or Excel 2019. If you open a file that uses it in those versions, the formula won't work, so use INDEX/MATCH for files you share with people on older Excel.
Example 1: fill in prices, with a missing code
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Order | Code | Price (VLOOKUP) | Price (INDEX/MATCH) | With IFNA |
| 2 | 5001 | P-100 | 8.5 | 8.5 | 8.5 |
| 3 | 5002 | P-103 | 39 | 39 | 39 |
| 4 | 5003 | P-999 | #N/A | #N/A | Not found |
| 5 | 5004 | P-102 | 6.25 | 6.25 | 6.25 |
The formula in E2 replaces #N/A with a message but lets other errors through, so a real mistake still shows:
=IFNA(VLOOKUP(B2,Prices!$A$2:$C$6,3,FALSE),"Not found")The XLOOKUP formula above gives the same results as column E without IFNA, because "Not found" is its fourth argument. IFNA itself works in Excel 2016 and later; more on it in how to use IF in Excel.
Example 2: approximate match for bands
Approximate match is useful when you're looking up a band, not an exact value: a discount by quantity, a tax bracket, a shipping rate by weight. Put the lower limit of each band in the first column, sorted smallest to largest, and use TRUE:
=VLOOKUP(B2,$E$2:$F$5,2,TRUE)| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Customer | Quantity | Discount | From qty | Discount | |
| 2 | Harbor Cafe | 7 | 0 | 0 | 0 | |
| 3 | Ortiz Dental | 10 | 0.05 | 10 | 0.05 | |
| 4 | Pine Library | 64 | 0.1 | 50 | 0.1 | |
| 5 | Summit Gym | 250 | 0.15 | 100 | 0.15 |
With XLOOKUP, set the fifth argument (match_mode) to -1, meaning "exact match, or the next smaller item":
=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)The band table should start at the lowest possible value (0 here). A quantity below the first band gives #N/A.
Which one to use
| VLOOKUP | INDEX/MATCH | XLOOKUP | |
|---|---|---|---|
| Excel versions | All | All | Microsoft 365, 2021, 2024, web |
| Default match | Approximate (add FALSE) | Set by MATCH (use 0) | Exact |
| Return a column to the left | No | Yes | Yes |
| Breaks if a column is inserted | Can (fixed column number) | No | No |
| Not-found message | Wrap in IFNA | Wrap in IFNA | Built in |
Why you get #N/A (and other errors)
- The value really isn't there. Check the spelling, and check that the table range covers every row. Use whole columns (
Prices!A:C) if the list keeps growing. - Extra spaces. "P-100 " with a trailing space doesn't match "P-100". Use
TRIM(B2)as the lookup value, or clean the data with the steps in how to clean up text in Excel. - Numbers stored as text. Microsoft lists this as a common cause: the ID 1002 typed as text won't match the number 1002. Use
VALUE(B2)instead of B2 as the lookup value to turn text into a number, or convert the whole column. - Codes that lost their leading zeros. If one table came from a CSV, Excel may have turned 00123 into 123. Import it with the zeros kept, for example with CSV to Excel, which leaves codes like these as text.
- Approximate match on an unsorted list gives wrong answers or #N/A. Sort the first column, or use an exact match.
- #REF! in VLOOKUP means the column number is larger than the number of columns in the table.
- #NAME? means a misspelled function or sheet name, text without quotation marks, or XLOOKUP in a version that doesn't have it.
In short
Use XLOOKUP if everyone who opens the file has Microsoft 365 or Excel 2021 or later. Otherwise use VLOOKUP with FALSE, or INDEX/MATCH when you need to look left or want formulas that survive inserted columns. Use approximate match only for sorted band tables, and when you see #N/A, check for spaces and text-versus-number mismatches first.