TBT Bruce StudioPractical business resources

Spreadsheet help · Free guide

VLOOKUP showing #N/A or #REF!? Diagnose it before changing the workbook.

Two common Excel errors can look alike, but they mean different things. This walkthrough shows how to test a lookup, check the range and avoid hiding a real data problem.

T Bruce Studio practical guide · Excel lookup troubleshooting · Reviewed 2026-10-30

Start with a copy. Save a duplicate of the original workbook and note the cell, the formula and the expected value. If someone else maintains this file, find out what its columns mean before making changes.

1. Identify the error rather than guessing

What you seeLikely questionSafest first check
#N/AWas there no exact match, or does the lookup key have a different type or unexpected spaces?Search the lookup column for the exact value; inspect text versus number formatting.
#REF!Does the formula point outside the lookup range or to deleted cells?Count the columns in the range and compare the return-column number.
#VALUE!Are the formula's inputs or data types incompatible?Check argument types and whether a number is stored as text.

2. Check a VLOOKUP formula one argument at a time

Suppose A2 contains an item code, and a reference table has codes in column F and descriptions in column G. An exact-match lookup is:

=VLOOKUP(A2,$F$2:$G$100,2,FALSE)

Here A2 is the value to find, $F$2:$G$100 is the lookup range, 2 requests the second column of that range, and FALSE requires an exact match. The first column of the selected range must contain the lookup key.

For comparison, this requests a third return column from a two-column range:

=VLOOKUP(A2,$F$2:$G$100,3,FALSE)

That can produce #REF!. If the description is in column G, change the index to 2; do not extend the range unless the intended answer really is in a different column.

3. When the formula returns #N/A

  1. Verify the value exists. Search the actual first column of the lookup range, not the entire workbook. Check for unexpected punctuation and spelling.
  2. Check exact-match mode. For identifier lookups, use FALSE unless an approximate lookup is intentional and validated against sorted reference data.
  3. Check the data type. The number 123 and the text "123" may not match as expected. A product identifier with leading zeros, such as 00123, should often remain text. Never convert identifiers to numbers without understanding the source system.
  4. Look for invisible spaces. Compare the length of suspicious values. On a separate helper column, =TRIM(F2) removes ordinary excess spaces; some nonbreaking spaces require =TRIM(SUBSTITUTE(F2,CHAR(160)," ")). Review any cleaned results before replacing source values.
  5. Confirm the correct table. If two lists represent different reporting dates, do not silently force a match just to clear the error.
Do not treat IFERROR as a repair. =IFERROR(VLOOKUP(...),"") can hide errors in a report, but it does not explain or correct their cause. Diagnose the failed lookup first.

4. When the formula returns #REF!

Inspect whether a row or column was deleted, whether the return index exceeds the range width, or whether an external reference is no longer valid. If the damage was just made, Undo may recover the original formula. Otherwise compare the expression with a known-good version or workbook backup.

5. Verify the fix against real records

  • Test an identifier known to exist, one known to be missing and one with leading zeros.
  • Check whether the returned description or price matches the source table—not merely whether the error disappeared.
  • Recalculate after copying the formula down, and check the top and bottom of the range.
  • Keep the original file until the repaired copy has been reviewed.

Try the formula in a private checker

The free Spreadsheet First Aid tool can identify selected VLOOKUP range-index mistakes and other structural risks without uploading the workbook. It cannot calculate the full workbook or infer business rules.

For an eligible simple file that needs cleaning or repair, see Spreadsheet Rescue · R990. Work that requires rebuilding a complex financial model or deciding uncertain formulas is outside the unattended repair scope.

Reference documentation

See Microsoft's support notes on #N/A lookups and #REF! reference errors.

More practical resources