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)

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.

NeedBest choiceWhy
Simple lookup, table starts with the keyVLOOKUPShort and familiar
Look left of the key columnXLOOKUP or INDEX MATCHVLOOKUP cannot look left
Column inserted laterXLOOKUP or INDEX MATCHVLOOKUP breaks when the column number changes
Return several columns at onceXLOOKUPOne formula can spill multiple results
Older Excel versions (before 2021)INDEX MATCHXLOOKUP 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 us

More 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