Excel troubleshooting

Why Is VLOOKUP Returning #N/A?

Fix VLOOKUP #N/A errors by checking exact match, lookup values, hidden spaces, data types, and the first column of the table array.

Problem

  • VLOOKUP returns #N/A when Excel cannot find the lookup value in the first column of the table array.

Quick fix

  • Use FALSE for exact match, confirm the lookup value exists in the first column, and clean hidden spaces or mismatched data types.

Most common causes

  • The lookup value is not in the first column of the table_array.
  • The fourth argument is omitted or TRUE, so VLOOKUP is using approximate match.
  • One value is text and the other is a number, even if they look identical.
  • Imported data contains leading spaces, trailing spaces, or hidden characters.

Best fixes

  • Use =VLOOKUP(value, table, column, FALSE) for exact matching.
  • Use TRIM and CLEAN on imported text before lookup formulas.
  • Convert text numbers with VALUE or convert numbers to text with TEXT when needed.
  • Consider XLOOKUP for clearer not-found handling.

Overview

  • #N/A means Excel did not find an exact match (with FALSE) or could not use approximate match correctly. Fix the data and logic first — then use IFNA only for display.

Fix in this order (fastest first)

  • 1. Confirm lookup value exists in the first column of table_array.
  • 2. Verify fourth argument is FALSE for IDs and codes.
  • 3. TRIM/CLEAN both sides; check number vs text (green triangle).
  • 4. Re-count col_index_num after inserting columns.
  • 5. Lock table_array with $ so copy-down does not shift the range.

Test formulas to isolate the row

  • Helper: =EXACT(TRIM(G2),TRIM(A2)) copied down the table.
  • Type check: =ISNUMBER(G2) and =ISNUMBER(A2) on failing rows.
  • Compare: =MATCH(G2,A:A,0) — if MATCH is #N/A, VLOOKUP will be too.

After the match works

  • Display blank: =IFNA(VLOOKUP(...),"")
  • Modern alternative: [XLOOKUP](/functions/xlookup/) with if_not_found.
  • Reference: [VLOOKUP function](/functions/vlookup/) · [Fix #N/A hub](/errors/na/).

Quick answer: vlookup returning na

  • VLOOKUP returns #N/A when no match is found. Check FALSE for exact match, first-column rule, TRIM/CLEAN on text, and number-stored-as-text before using IFERROR.
  • Fastest checks: exact match flag, col_index_num, spaces.
  • Compare lookup value type with INDEX/MATCH test column.
  • Full function reference: [VLOOKUP](/functions/vlookup/).

10-point lookup debug checklist

  • 1. Is the lookup value present in the first column of table_array?
  • 2. Is the fourth argument FALSE for exact match?
  • 3. Are leading or trailing spaces present? Use TRIM.
  • 4. Is one value a number and the other text?
  • 5. Is col_index_num still correct after inserting columns?
  • 6. Are sheet names and external links intact?
  • 7. Is the table_array reference locked and complete?
  • 8. Are hidden characters present? Try CLEAN.
  • 9. Does approximate match (TRUE) make sense for this data?
  • 10. After fixes, test with a helper column =EXACT(lookup,table_cell).

TRIM, CLEAN, and TEXT fixes

  • Helper column: =TRIM(CLEAN(A2)) on both lists before matching.
  • Convert text numbers: =VALUE(A2) or format consistently before lookup.
  • Prominent next steps: [VLOOKUP function](/functions/vlookup/) and [VLOOKUP tutorial](/blog/vlookup-excel-tutorial/).

People also ask

  • VLOOKUP #N/A but value exists? — Check spaces, types, exact match, and first-column rule.
  • How to show blank instead of #N/A? — IFNA or IFERROR after fixing logic, or XLOOKUP if_not_found.

Frequently asked questions

  • VLOOKUP #N/A but the value looks identical? Hidden spaces, non-printing characters, or text vs number are the top causes. Use TRIM and VALUE.
  • Should I use IFERROR before fixing #N/A? No. IFERROR hides audit problems. Fix the match, then add IFNA for user-friendly display.
  • VLOOKUP #N/A on some rows only? Those keys are missing from the first column or fail exact match. Use MATCH to list missing keys.
  • Should I use IFERROR before fixing VLOOKUP #N/A? Fix the root cause first (spaces, types, exact match). Use IFERROR or IFNA only after the lookup logic is correct.
  • VLOOKUP #N/A but XLOOKUP works — why? Usually FALSE vs default match behavior, or col_index_num pointing at the wrong column after inserts.
  • How do I audit 500 rows of #N/A? Add a helper =EXACT(TRIM(lookup),TRIM(key)) or use MATCH to flag rows with no match before changing the main formula.