Fix #N/A Error in Excel

This error occurs when a value is not available to a function or formula. It's most common in lookup functions when the search value cannot be found.

Why this happens

  • VLOOKUP, HLOOKUP, or XLOOKUP cannot find the lookup value
  • MATCH function cannot find the lookup value
  • Array formulas have mismatched dimensions
  • Missing data in referenced ranges

How to fix it

  • Use IFERROR or IFNA to handle the error gracefully
  • Verify the lookup value exists in the data
  • Check for extra spaces or formatting differences
  • Ensure data types match (text vs. number)
  • Use XLOOKUP with if_not_found parameter

Overview

  • #N/A means “value not available.” In lookup formulas it almost always means no match was found. This hub focuses on VLOOKUP, XLOOKUP, and INDEX MATCH — the source of most #N/A searches.

Lookup-related #N/A (most common)

  • VLOOKUP/XLOOKUP: no exact match, wrong column, or type mismatch.
  • MATCH: value not in lookup_array with match_type 0.
  • INDEX with invalid MATCH position returns #N/A indirectly.

Non-lookup #N/A sources

  • Legacy CUBE functions without valid connection.
  • Broken links to closed workbooks.
  • Array formulas referencing deleted ranges.

Lookup cluster quick links

  • [VLOOKUP](/functions/vlookup/) · [XLOOKUP](/functions/xlookup/) · [INDEX MATCH guide](/blog/index-match-excel-guide/)
  • [VLOOKUP #N/A](/problems/vlookup-returning-na/) · [XLOOKUP not found](/problems/xlookup-not-finding-value/)
  • [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/)

High-impact #N/A checks

  • For lookup formulas, confirm the lookup value exists in the lookup range and that both values use the same data type.
  • Use TRIM or CLEAN when imported data may contain hidden spaces or non-printing characters.
  • Use IFNA or the XLOOKUP if_not_found argument only after confirming the lookup logic is correct.

Quick answer: excel na error

  • #N/A in Excel usually means a lookup did not find a match. Fix exact match, data types, spaces, and column index before hiding the error with IFERROR.
  • VLOOKUP: lookup value must be in the first column of table_array.
  • Test with TRIM on both sides or =EXACT(lookup, key).
  • Hub: [VLOOKUP #N/A problem](/problems/vlookup-returning-na/) · [XLOOKUP fixes](/problems/xlookup-not-finding-value/).

Printable lookup debug checklist

  • Exact match enabled? Hidden spaces? Text vs number? Correct column index? Table includes all rows?
  • Test with EXACT or a helper column before wrapping in IFERROR.

Hub links for lookup errors

  • [VLOOKUP](/functions/vlookup/) · [XLOOKUP](/functions/xlookup/) · [INDEX MATCH guide](/blog/index-match-excel-guide/)
  • [VLOOKUP returning #N/A](/problems/vlookup-returning-na/) · [XLOOKUP not finding value](/problems/xlookup-not-finding-value/)

People also ask

  • What causes #N/A in Excel? — Usually an unsuccessful lookup or missing value in MATCH/XLOOKUP/VLOOKUP.
  • How to hide #N/A? — Fix the match first; then IFNA, IFERROR, or XLOOKUP if_not_found for display.
  • VLOOKUP #N/A but value exists? — Check exact match, spaces, text vs number, and first-column rule.

Functions that often show this error

  • XLOOKUP: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • VLOOKUP: Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
  • MATCH: Returns the relative position of an item in an array that matches a specified value.
  • CONVERT: Converts a number from one measurement system to another.
  • HLOOKUP: Searches for a value in the top row of a table and returns a value in the same column from a row you specify.
  • XMATCH: Returns the relative position of an item in an array or range of cells. Supports exact match, approximate match, and wildcard matching.
  • IFS: Checks multiple conditions and returns a value corresponding to the first TRUE condition.
  • SWITCH: Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
  • ISERROR: Returns TRUE if the value is any error value.
  • ISNA: Returns TRUE if the value is the #N/A error.
  • TEXTAFTER: Returns text that occurs after a specified delimiter.
  • TEXTBEFORE: Returns text that occurs before a specified delimiter.
  • MODE.SNGL: Returns the most frequently occurring value in a data set.
  • FORECAST.LINEAR: Calculates a future value using linear regression.
  • FORMULATEXT: Returns the formula in a cell as text.
  • SHEET: Returns the sheet number of the referenced sheet.
  • IFNA: Returns a value you specify if a formula returns #N/A; otherwise returns the formula result.
  • PERCENTRANK.INC: Returns the rank of a value in a data set as a percentage (0..1, inclusive).
  • PERCENTRANK.EXC: Returns the rank of a value in a data set as a percentage (0..1, exclusive).
  • MODE.MULT: Returns a vertical array of the most frequently occurring values in a data set.
  • ERROR.TYPE: Returns a number corresponding to an error type.
  • CUBEVALUE: Returns an aggregated value from a cube.
  • CUBEMEMBER: Returns a member or tuple in a cube hierarchy.
  • CUBESET: Defines a calculated set of members by sending a set expression to the cube.