XLOOKUP vs VLOOKUP vs INDEX/MATCH: which to use, and what to do when nothing is found
Three ways to look up a value in Excel and Google Sheets, tested on sample data: which one to pick, how to look left, and how to show 'Not found' instead of #N/A.
The problem
You need to pull a price (or any value) from a table by its ID. Three formulas do it, and each behaves differently when a column moves, the ID is missing, or the file opens in an older Excel. Here is which to pick.
Sample data
Sheet1, rows 2-5 (D and E hold the values we look up):
| A ID | B Name | C Price | D Lookup ID | E Lookup name |
|---|---|---|---|---|
| P1 | Bolt | 2.5 | P2 | Nut |
| P2 | Nut | 1.2 | P9 | Cog |
| P3 | Washer | 0.4 | ||
| P4 | Screw | 3 |
D2 = P2 exists. D3 = P9 is missing. E2 = Nut exists. E3 = Cog is missing.
The 10 formulas and their results
| # | What it does | Formula | Result |
|---|---|---|---|
| 1 | Classic lookup | =VLOOKUP(D2,A2:C5,3,FALSE) | 1.2 |
| 2 | Modern lookup | =XLOOKUP(D2,A2:A5,C2:C5,"Not found") | 1.2 |
| 3 | Works everywhere | =INDEX(C2:C5,MATCH(D2,A2:A5,0)) | 1.2 |
| 4 | XLOOKUP, missing ID | =XLOOKUP(D3,A2:A5,C2:C5,"Not found") | Not found |
| 5 | INDEX/MATCH, missing ID | =IFERROR(INDEX(C2:C5,MATCH(D3,A2:A5,0)),"Not found") | Not found |
| 6 | VLOOKUP, missing ID | =IFERROR(VLOOKUP(D3,A2:C5,3,FALSE),"Not found") | Not found |
| 7 | Look left (name to ID), INDEX/MATCH | =INDEX(A2:A5,MATCH(E2,B2:B5,0)) | P2 |
| 8 | Look left, XLOOKUP | =XLOOKUP(E2,B2:B5,A2:A5) | P2 |
| 9 | Look left, missing name | =IFERROR(INDEX(A2:A5,MATCH(E3,B2:B5,0)),"Not found") | Not found |
| 10 | VLOOKUP cannot look left | =VLOOKUP(E2,A2:C5,1,FALSE) | #N/A |
Formula 10 searches the name in column A, where names are not, so it fails. VLOOKUP only looks to the right of the column it searches.
Which one to use
- XLOOKUP: pick it if you have Excel 2021, Microsoft 365 or Google Sheets. It looks in any direction, needs no column number, and has a built-in "not found" text (the 4th argument). VLOOKUP breaks when someone inserts a column, because its 3 is a position; XLOOKUP points at the column itself.
- INDEX/MATCH: pick it if the file must open in Excel 2019 or older, or go to people on old versions. It works in every Excel version and in Sheets, and it looks left too.
- VLOOKUP: fine for quick one-offs and files that already use it. Always end with FALSE (exact match). Leaving it out means approximate match, which gives wrong answers on unsorted data.
When the ID is missing
VLOOKUP and INDEX/MATCH return #N/A, so wrap them in IFERROR(..., "Not found"). Careful: IFERROR hides every error, not just "not found", so wrap only the lookup itself. XLOOKUP needs no wrapper.
Excel vs Sheets
- XLOOKUP: the checker warns that it needs Excel 2021 or Microsoft 365; older Excel shows #NAME?. Google Sheets added XLOOKUP in 2022 with the same argument order (what to find, where to look, what to return, text if not found). We tested it on sample data in the checker, not inside the real Google Sheets app.
- VLOOKUP and INDEX/MATCH: identical in Excel and Sheets.
- Commas or semicolons: the examples use commas. Some European-language Excel or Sheets files use semicolons between arguments. If a formula is rejected, swap them.
- Case: all three ignore upper and lower case.
- Number vs text: if an ID like 101 is a number in one range and text in the other, all three return #N/A. Fix it by retyping the column or wrapping the value in VALUE().
Want it done for you?
The Cellwise freelancer invoice and finance tracker template uses a product lookup like this. (Listing link will be added when the template is live.)
Tested on sample data by the Cellwise checker: 10 of 10 checks passed, run fvfix-20261021T024744Z-a2a650be2887, log CW-PL-20261021-57. AI-made: the text, voice and visuals are generated by AI.
Checked before publishing: a check.