Seongwoo Choi / Journal

Excel XLOOKUP·VLOOKUP #N/A errors: Check the lookup value first, then handle blanks

At a glanceAn #N/A error means Excel cannot find the specified value in the lookup range. This article outlines Microsoft’s recommended checks for XLOOKUP and VLOOKUP, using illustrative examples rather than a reproduced worksheet.

Conceptual illustration for Excel XLOOKUP·VLOOKUP #N/A errors: Check the lookup value first, then handle blanks.
AI-generated editorial illustration; not a product screenshot.
In this article 5 sections
  1. 1. Check whether the lookup value actually exists in the source range
  2. 2. Match number and text formats and remove spaces
  3. 3. Confirm that the lookup is for an exact match
  4. 4. Display a value only for rows that are legitimately missing
  5. 5. Summary of the checking order

An #N/A error in Excel means that the function cannot find the value it was instructed to look for in the lookup range. Based on Microsoft support documents, this article organizes the checks to make when XLOOKUP or VLOOKUP returns #N/A. The worksheet was not opened and tested directly, and the table and formulas below are hypothetical examples for illustration.

1. Check whether the lookup value actually exists in the source range

The most common cause is that the lookup value does not exist in the source data. For example, if the lookup value is “banana” but the price list has no banana row, VLOOKUP returns #N/A. Copy the value from the lookup cell and search for it in the source range with Ctrl+F. Even values that look identical may fail to match because of trailing spaces or different characters.

2. Match number and text formats and remove spaces

If the lookup value is a number but the value in the source table is stored as text, the two values may not be recognized as equal, resulting in #N/A. Microsoft’s documentation recommends checking the cell format first and, if necessary, shows an example of looking up a range with leading and trailing spaces removed using the TRIM function. Before changing the source column directly, it is safer to check the result in an adjacent column.

3. Confirm that the lookup is for an exact match

If the last argument of VLOOKUP is TRUE or omitted, it attempts to find an approximate match when no exactly matching value exists. This can produce #N/A, or an incorrect result even when the exact value exists. To find an exact match, use FALSE, as in =VLOOKUP(lookup_value, table_range, column_number, FALSE). XLOOKUP uses exact match by default, so this issue is less common, but it still returns #N/A when it cannot find a match.

4. Display a value only for rows that are legitimately missing

If an item is legitimately absent from the list, it is better to define the result in advance than to hide the error. In XLOOKUP, you can enter text such as “Not found” in the fourth argument, if_not_found. Example: =XLOOKUP(A2, code_column, name_column, "Not found"). XLOOKUP cannot be used in versions 2016 and 2019, so you can use IFNA to replace only #N/A, as in =IFNA(VLOOKUP(A2, table_range, 2, FALSE), "Not found"). Because IFERROR hides all errors, be careful not to conceal errors such as #REF! caused by an incorrect formula range.

5. Summary of the checking order

① Search for the lookup value in the source range. ② Check the number or text format and leading or trailing spaces. ③ Set the last argument of VLOOKUP to FALSE. ④ Add a label such as “Not found” only for rows that are legitimately missing. If the issue is still unresolved after these checks, finally verify that the range referenced by the formula is correct and that the sheet name has not changed.

The official documents referenced are Microsoft Support: How to correct a #N/A error and Microsoft Support: XLOOKUP function . Menu names and function display may vary depending on the Excel version and language settings.

Microsoft Excel worksheet with XLOOKUP returning Brazil’s +55 dial code from a country table.
Microsoft Excel example: =XLOOKUP(F2,B2:B11,D2:D11) looks up Brazil in column B and returns +55 from column D. This demonstrates a successful match; check that your lookup value exists before handling #N/A. Source: Microsoft Support. Used with permission from Microsoft. Reuse terms.

Sources and original article

Read the Korean original on Naver

This article reflects public information available when it was written. Specifications, prices and schedules can change; check the linked official documentation.

This English edition was written and translated with AI assistance, with sources provided. For errors or additional information, please request a correction. Editorial policy