Your VLOOKUP formula should return one clean match, but Excel shows #N/A, #REF!, #VALUE!, #NAME?, #SPILL!, an old result, or the wrong value. The fix depends on what failed: the match setting, the lookup range, the return-column number, dirty data, calculation mode, or a linked workbook. Start with the exact-match and range checks, then move to the error-specific fixes that match what Excel is showing.
1. Force an exact match first
Wrong results often come from the fourth VLOOKUP argument. When range_lookup is TRUE or left blank, Excel uses approximate matching, and the first lookup column must be sorted ascending.
- 1.Select the cell that contains the VLOOKUP formula.
- 2.Click in the formula bar and find the fourth argument.
- 3.Change it to FALSE or 0, using this pattern:
=VLOOKUP(lookup_value,table_array,col_index_num,FALSE). - 4.Press Enter.
For a normal lookup where the ID, name, SKU, or value must match exactly, use FALSE. If you intentionally use approximate matching, sort the first lookup column in ascending order. If you want one exact item back, leave the formula on FALSE.
2. Point VLOOKUP at the correct column
VLOOKUP searches only the first column of table_array. If the value you are matching is in column C, the lookup range must start with column C. A range that starts in column A will not search column C as the lookup column.
Check the formula from left to right: lookup_value is the value to find, table_array is the source range, col_index_num is the return column counted from the left edge of that selected range, and range_lookup is the match setting. Exact-match formulas use FALSE.
Example: if table_array is A2:D5, the valid return-column numbers are 1, 2, 3, and 4. A col_index_num of 5 returns #REF!. A col_index_num of 0, text, or a value under 1 returns #VALUE!.
3. Clean the data behind #N/A
- Confirm the lookup value exists in the first column of table_array.
- Check spelling in the lookup cell and the source data.
- Match the data types when one side is a number and the other side is stored as text.
- Remove leading and trailing spaces with TRIM.
- Remove nonprinting or inconsistent characters with helper formulas using TRIM, CLEAN, and SUBSTITUTE.
#N/A means Excel did not find the lookup value in the first column of the selected range. These simple checks catch the fastest failures.
For numbers stored as text, select the affected lookup and source cells, right-click, choose Format Cells, open Number, and choose the same number format where appropriate. To convert stored-as-text numbers, use the warning indicator conversion, VALUE(), Paste Special with multiply by 1, or Data then Text to Columns.
In current Microsoft 365 dynamic-array Excel, use a cleaned lookup like =VLOOKUP(D2,TRIM(A2:B7),2,FALSE) and press Enter. In older non-dynamic-array Excel builds, confirm that array formula with Ctrl + Shift + Enter.
4. Match the fix to the displayed error
#REF! points to the return-column number. Count columns from the left edge of table_array, then change col_index_num to a number inside that range.
#VALUE! points to an invalid col_index_num or a lookup value longer than 255 characters. Set col_index_num to a number of at least 1. For a lookup value over 255 characters, shorten the lookup value or use an INDEX and MATCH array workaround such as =INDEX(return_range,MATCH(TRUE,lookup_range=lookup_value,0)). In current Microsoft 365, press Enter; in older Excel builds, confirm the array formula with Ctrl + Shift + Enter.
#NAME? points to missing quotes or a mistyped name. Put literal text inside straight double quotes, as in =VLOOKUP("Fontana",B2:E7,2,FALSE). For a defined name, go to Formulas, Defined Names, Use in Formula, then insert the correct name.
#SPILL! appears in dynamic-array Excel when the lookup value is a whole-column reference such as =VLOOKUP(A:A,A:C,2,FALSE). Change the lookup value to a single cell, such as =VLOOKUP(A2,A:C,2,FALSE), or use implicit intersection with =VLOOKUP(@A:A,A:C,2,FALSE).
5. Lock the range before copying
Lock table_array with absolute references before copying the formula.
- 1.Select the first VLOOKUP formula cell.
- 2.Change the source range from a relative reference such as
A2:D100to an absolute reference such as$A$2:$D$100. - 3.Press Enter.
- 4.Fill the formula down or across again.
A VLOOKUP that works in the first row and fails after filling down has a drifting source range. The lookup cell can still move row by row, but the source range stays fixed.
6. Recalculate stale formulas
- 1.Go to Formulas, Calculation Options, then choose Automatic.
- 2.To recalculate immediately, press F9, or go to Formulas, Calculation, Calculate Now.
- 3.To recalculate the active sheet only, choose Calculate Sheet or press Shift + F9.
- 4.If formulas display instead of results, go to Formulas, Formula Auditing, then turn off Show Formulas. On Windows, press Ctrl + `.
If the formula is correct but the result is old, turn calculation back on and force a recalculation.
In Microsoft 365 versions that support stale value formatting, select the warning icon next to the stale cell and choose Calculate Now. You can manage that indicator through Formulas, Calculation Options, Format Stale Values.
7. Inspect the formula with Excel tools
Use Excel's built-in auditing tools when the formula still looks right. Select the VLOOKUP cell, go to Formulas, then choose Insert Function. Review lookup_value, table_array, col_index_num, and range_lookup in the function wizard.
For step-by-step inspection, choose Formulas, Formula Auditing, Evaluate Formula, then Evaluate. Use Step In and Step Out when Excel offers them for referenced formulas.
To see the cells connected to the formula, choose Trace Precedents or Trace Dependents. Choose Remove Arrows when you finish. To run a broader worksheet check, choose Formulas, Formula Auditing, Error Checking. To recheck ignored errors on Windows, go to File, Options, Formulas, Error Checking, Reset Ignored Errors, then OK.
8. Repair linked workbook lookups
- 1.Open Data, Queries and Connections, then Workbook Links.
- 2.Select Refresh all, or choose one workbook and select Refresh.
- 3.If the source file moved, select More Commands, Change source, browse to the source file, and select the new source file.
- 4.If Excel shows a Security Warning bar for external content, select Enable Content.
When VLOOKUP pulls from another workbook, broken links and blocked external content stop the formula from updating correctly.
Use Break links only when you want to remove the external formula connection. Go to Data, Queries and Connections, Workbook Links, then choose Break links. Excel converts formulas that use the external source to their current values, and the change cannot be undone except from a saved backup or immediate Undo where available.
Do not use old instructions that rely on Data then Edit Links as the main fix. Microsoft documents Workbook Links as the replacement. On managed work or school devices, admins can block changes to external content settings.
9. Replace VLOOKUP when it is the problem
XLOOKUP is the better choice for new formulas in Excel versions that support it. It looks in any direction and returns exact matches by default, which removes two major VLOOKUP failure points. Use this pattern: =XLOOKUP(lookup_value,lookup_array,return_array,"Not found").
Use INDEX and MATCH when the workbook layout requires a left lookup. The ordinary exact-match pattern is =INDEX(return_range,MATCH(lookup_value,lookup_range,0)). When the lookup value is longer than 255 characters, use the array pattern =INDEX(return_range,MATCH(TRUE,lookup_range=lookup_value,0)) instead, because passing the long text as MATCH's lookup_value hits the same 255-character limit. In current Microsoft 365, press Enter. In older array-formula workarounds, use Ctrl + Shift + Enter.
After the lookup is correct, control what users see. Use =IFNA(VLOOKUP(lookup_value,table_array,col_index_num,FALSE),"Not found") to replace only #N/A. Use =IFERROR(VLOOKUP(lookup_value,table_array,col_index_num,FALSE),"Error message here") only after verifying the formula logic, because it replaces every displayed error.
Frequently Asked Questions
Why does VLOOKUP return the wrong value instead of #N/A?
The formula is using approximate matching. Add FALSE or 0 as the fourth VLOOKUP argument. If you intentionally use TRUE or omit the fourth argument, sort the first lookup column ascending.
Why does VLOOKUP break after I copy it down?
The table_array moved as the formula was filled. Lock the source range with absolute references, such as $A$2:$D$100, before copying or filling the formula.
How do I hide only #N/A in VLOOKUP?
Wrap the formula with IFNA, such as =IFNA(VLOOKUP(...),"Not found"). IFNA replaces #N/A while leaving other formula errors visible.
When should I use XLOOKUP instead of VLOOKUP?
Use XLOOKUP for new formulas when your Excel version supports it. XLOOKUP looks in any direction and uses exact matching by default.
