Excel VLOOKUP Error Fix Guide: From #N/A and #REF! to Approximate Match Issues
This guide explains how to quickly fix Excel VLOOKUP errors. It provides practical recipes for exact matches, approximate matches, wildcards, left lookup alternatives (INDEX/MATCH and XLOOKUP), and a checklist of seven common mistakes.
Quick Fix
=VLOOKUP([@Item], $B$3:$E$7, 4, FALSE)=IFERROR(VLOOKUP([@Item], $B$3:$E$7, 4, FALSE), "Not found")=VLOOKUP([@Qty], $H$3:$I$7, 2, TRUE)=INDEX(PriceList[Item], MATCH("Pro", PriceList[Tier], 0))Sample Data
| Item | Name | Price | Tier |
|---|---|---|---|
| T001 | T-Shirt | 19000 | Basic |
| T002 | Polo | 25000 | Plus |
| B014 | Pants | 35000 | Basic |
| O010 | Jacket | 159000 | Pro |
| S201 | Shoes | 140000 | Pro |
Exact Match
=VLOOKUP([@Item], PriceList[[Item]:[Price]], 3, FALSE)Approximate Match
=VLOOKUP([@Qty], $H$3:$I$7, 2, TRUE)Wildcards
=VLOOKUP("*Shirt*", PriceList[[Name]:[Price]], 2, FALSE)Left Lookup and Multiple-Criteria Alternatives
=INDEX(PriceList[Item], MATCH("Pro", PriceList[Tier], 0))=XLOOKUP("Pro", PriceList[Tier], PriceList[Item], "Not found")Seven Errors and Fixes
| Error/Symptom | Cause | Fix |
|---|---|---|
| #N/A | Key mismatch | Use TRIM/CLEAN, standardize formats, and use IFERROR |
| #REF! | Column index exceeds the range | Correct the column index or use INDEX/MATCH/XLOOKUP |
| Incorrect value | Approximate-match sorting requirement not met | Sort ascending or use FALSE |
| Range shifts | Relative references | Use $ absolute references or a table |
| Only the first duplicate match is returned | Design limitation | Use sorting/priorities or XLOOKUP |
| Cannot return a value to the left | Design limitation | Use INDEX/MATCH or XLOOKUP |
| Wildcard issues | Contains */? | Escape with ~ |
Conclusion
Understanding VLOOKUP’s limitations helps you resolve errors quickly. When possible, switch to XLOOKUP for more reliable lookups.