VLOOKUP Not Working: 8 Fixes for #N/A and Wrong Results
If VLOOKUP returns #N/A, #REF! or the wrong value, one of eight causes is almost always responsible. Work through this checklist from the top.
Updated 18 Nov 2026 · By Softotech Solutions
- 1. Use exact match (FALSE or 0)
- 2. Remove extra spaces
- 3. Match number and text formats
- 4. The lookup value must be in the first column
- 5. Check the column index number
- 6. Lock the table range
- 7. Check calculation mode and hidden problems
- 8. Handle the error on purpose
- VLOOKUP with multiple criteria
- VLOOKUP, XLOOKUP and INDEX MATCH compared
- Approximate match example: tiered rates
- Wildcards and partial matches
- A quick troubleshooting flow
1. Use exact match (FALSE or 0)
The fourth argument decides whether VLOOKUP looks for an exact or approximate match. Leave it out and Excel assumes approximate, which needs a sorted first column and often returns wrong values. For most lookups use =VLOOKUP(A2,$E$2:$G$100,2,FALSE).
2. Remove extra spaces
A trailing space makes “ABC” and “ABC ” different values. Clean the lookup column or the lookup value with =TRIM(A2). Text copied from websites or exports can also hold non-breaking spaces. Try =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).
3. Match number and text formats
The number 1001 and the text “1001” do not match. Signs of text numbers: left-aligned values and a small green triangle. Convert text to numbers by multiplying by 1 (=A2*1), using =VALUE(A2), or selecting the column and using Data, Text to Columns, Finish. To look up a number in a text column, convert the lookup value with =VLOOKUP(A2&"",...).
4. The lookup value must be in the first column
VLOOKUP searches only the first column of the table array and returns values to the right. If your ID column is not the leftmost, move it, or use XLOOKUP or INDEX MATCH, which can look left.
5. Check the column index number
The third argument counts columns inside the table array, not on the sheet. An index larger than the number of columns returns #REF!. An index of 0 or less returns #VALUE!.
6. Lock the table range
When you copy a formula down, an unlocked range slides and misses the data. Use dollar signs: $E$2:$G$100. See absolute references in Excel.
7. Check calculation mode and hidden problems
If results do not update, go to Formulas, Calculation Options and select Automatic. Also confirm the lookup value really exists in the table, including spelling and leading zeros, and that you are not searching a filtered or different sheet range by mistake.
8. Handle the error on purpose
If a missing value is expected, wrap the formula: =IFERROR(VLOOKUP(A2,$E$2:$G$100,2,FALSE),"Not found"). Use this after you have fixed real causes, because IFERROR also hides genuine mistakes.
VLOOKUP with multiple criteria
VLOOKUP can match only one value. To match two, such as customer and month, add a helper column to the left of your table that joins the two keys: =A2&"|"&B2. Then look up the joined value: =VLOOKUP(F2&"|"&G2,$D$2:$H$100,4,FALSE). In Microsoft 365 you can skip the helper column with XLOOKUP, covered in our guide to XLOOKUP with multiple criteria. The full basics are in How to use VLOOKUP.
VLOOKUP, XLOOKUP and INDEX MATCH compared
If you keep running into VLOOKUP limits, the table below shows when a different function is a better choice.
| Need | Best choice | Why |
|---|---|---|
| Simple lookup, table starts with the key | VLOOKUP | Short and familiar |
| Look left of the key column | XLOOKUP or INDEX MATCH | VLOOKUP cannot look left |
| Column inserted later | XLOOKUP or INDEX MATCH | VLOOKUP breaks when the column number changes |
| Return several columns at once | XLOOKUP | One formula can spill multiple results |
| Older Excel versions (before 2021) | INDEX MATCH | XLOOKUP is not available |
Read XLOOKUP in Excel and INDEX MATCH for step-by-step examples.
Approximate match example: tiered rates
Approximate match is useful for tiers such as discounts or commission rates. Make a table with the lower limit of each tier in the first column, sorted ascending: 0, 1000, 5000 and 10000, with rates 0%, 2%, 4% and 6%. Then =VLOOKUP(B2,$F$2:$G$5,2,TRUE) finds the largest limit that is less than or equal to B2. A sale of 5,200 returns 4%. If the table is not sorted, results will be wrong, which is one of the main reasons people see unexpected values.
Wildcards and partial matches
VLOOKUP accepts wildcards when the last argument is FALSE. =VLOOKUP("*"&A2&"*",$E$2:$G$100,2,FALSE) finds a row whose first column contains the text in A2 anywhere. Use =VLOOKUP(A2&"*",$E$2:$G$100,2,FALSE) to match names that start with the text in A2. Wildcards work only with text, not numbers.
A quick troubleshooting flow
Work through these questions in order: Does the value exist in the first column of the table? Are both sides the same type, number or text? Are there stray spaces? Is the fourth argument FALSE? Is the column number inside the table range? Is the range locked with dollar signs? Fixing them in this order solves most cases within a minute. If you need help cleaning a large messy file, our team can automate it through our Excel consultant service.
Frequently asked questions
Why does VLOOKUP return #N/A?
The lookup value was not found. Common reasons are extra spaces, number versus text mismatch, a misspelling, or approximate match on unsorted data.
Why is VLOOKUP returning the wrong value?
The fourth argument is probably missing or TRUE, so Excel uses approximate match. Set it to FALSE.
Can VLOOKUP look to the left?
No. It returns values only from columns to the right of the lookup column. Use XLOOKUP or INDEX MATCH to look left.
How do I do a VLOOKUP with two criteria?
Add a helper column that joins both keys, then look up the joined value. Or use XLOOKUP in Microsoft 365.
What does #REF! mean in VLOOKUP?
The column index number is larger than the number of columns in the table array.
Official reference: VLOOKUP function (Microsoft Support)
Need Excel work done for you?
Talk to usMore free tools: How to use VLOOKUP · XLOOKUP in Excel · INDEX MATCH · All free calculators
Guides: How to VLOOKUP From Another Sheet in Excel · Excel Formula Not Calculating? 9 Fixes for Formulas That Show Text or Do Not Update · How to Use IFERROR in Excel