Fixes / Excel · Checked

Excel #N/A error: what it means and how to fix it

Short answer

#N/A means "value not available". Usually VLOOKUP, XLOOKUP, HLOOKUP, LOOKUP or MATCH could not find what you asked it to look up. The most common fix is to make both sides match exactly: the lookup value and the table must be the same type (both numbers or both text) with no extra spaces, and VLOOKUP's last argument should be FALSE.

What this message means

#N/A is Excel's "not available" error. In most cases a lookup formula (VLOOKUP, XLOOKUP, HLOOKUP, LOOKUP, MATCH or INDEX/MATCH) went through the range you gave it and found no exact match for the lookup value. Sometimes the value really is missing. More often it is there but looks different to Excel: a number stored as text, a trailing space, or a hidden character copied from a web page or another system. Less often, #N/A comes from an array formula whose ranges are different sizes, a cell that contains NA() on purpose, or a user-defined function from a workbook that is not open. An #N/A in one cell also passes through to any formula that refers to it.

Common causes

CauseHow to tell
The lookup value genuinely does not exist in the lookup rangePress Ctrl+F (Cmd+F on Mac), search the lookup column for the exact value, and Excel reports that it cannot find it. Other rows of the same formula work fine.
Numbers stored as text on one side and real numbers on the otherThe value looks identical in both places, but one side is left-aligned or has a small green triangle in the cell corner. =ISNUMBER(A2) returns TRUE for one cell and FALSE for the other. This is common with IDs, ZIP codes and data exported from other systems.
Extra spaces or hidden characters in the lookup value or the table=LEN(A2) returns a larger number than the visible characters. =A2=D5 returns FALSE even though both cells look the same. Data pasted from websites often contains non-breaking spaces that TRIM alone does not remove.
Approximate match used by mistake, or a match type that doesn't fit how the data is sortedThe VLOOKUP or HLOOKUP has no fourth argument or TRUE as the fourth argument, or MATCH has match_type 1, -1 or nothing at all. Some rows return #N/A and others return the wrong value, especially when the data is not sorted.
The lookup range is too small or points at the wrong columnThe range stops above the last rows of data (often because it isn't locked with $ and moved when you copied the formula down), or the value you are searching for is not in the first column of the VLOOKUP table.
NA() placeholders, array size mismatches or a missing user-defined functionClicking the source cell shows =NA() or a typed #N/A, the formula is an older array formula (curly braces { } in the formula bar) that spans a different number of rows than its source, or the formula calls a custom function from an add-in or workbook that is closed.

How to fix it

1. Use an exact match

  1. Click the cell showing #N/A and look at the formula in the formula bar.
  2. For VLOOKUP or HLOOKUP, make sure the last argument is FALSE, for example =VLOOKUP(A2,$D$2:$F$500,3,FALSE).
  3. For MATCH, set the third argument to 0, for example =MATCH(A2,$D$2:$D$500,0).
  4. XLOOKUP uses exact match by default. Leave match_mode empty or set it to 0.
  5. Only use approximate match (TRUE, 1 or -1) for deliberate range lookups such as tax brackets. The lookup column must then be sorted ascending (1/TRUE) or descending (-1).

2. Convert numbers stored as text so both sides match

  1. Test both sides with =ISNUMBER(A2) and =ISNUMBER(D2). If one returns TRUE and the other FALSE, the types don't match.
  2. Select the text-number cells, click the yellow warning icon that appears next to them, and choose Convert to Number.
  3. If there is no warning icon, select the column and go to Data > Text to Columns, then click Finish. This converts the whole column in one step.
  4. To leave the data unchanged, convert inside the formula instead: =VLOOKUP(A2*1,...) when the table holds real numbers, or =VLOOKUP(TEXT(A2,"0"),...) when the table holds text.
  5. Note that changing the number format through Format Cells alone does not convert values that are already in the cells. Re-enter them or use one of the methods above.

3. Remove extra spaces and hidden characters

  1. Check the length with =LEN(A2) and compare it with the visible text.
  2. Clean the lookup value inside the formula: =VLOOKUP(TRIM(A2),$D$2:$F$500,3,FALSE).
  3. If the table itself has stray spaces, add a helper column with =TRIM(CLEAN(D2)), then copy it and use Paste Special > Values over the original column.
  4. For data copied from the web, remove non-breaking spaces as well, which TRIM does not catch: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
  5. Alternatively, use Find and Replace (Ctrl+H on Windows) to replace a single space with nothing, but only in columns where values should never contain spaces.

4. Fix the lookup range

  1. Click the formula and check that the highlighted range covers every row of your data.
  2. Lock the range with dollar signs so it doesn't move when you copy the formula down: $D$2:$F$500 instead of D2:F500. Press F4 (Windows) or Cmd+T (Mac) while the cursor is in the reference to toggle this.
  3. For VLOOKUP, the column you search must be the first column of the range. If the value is to the right of the column you want to return, switch to XLOOKUP or INDEX/MATCH.
  4. Turn the source data into a table (Ctrl+T on Windows, Cmd+T on Mac) and refer to table columns, so new rows are included automatically.

5. Show a friendly result when a value is genuinely missing

XLOOKUP: Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web (not Excel 2019 or earlier). IFNA: Excel 2013 and later. IFERROR: all current versions.

  1. First make sure the formula works for values that do exist. Hiding the error too early also hides real mistakes.
  2. With XLOOKUP, use the if_not_found argument: =XLOOKUP(A2,$D$2:$D$500,$F$2:$F$500,"Not found").
  3. With VLOOKUP or MATCH, wrap the formula in IFNA: =IFNA(VLOOKUP(A2,$D$2:$F$500,3,FALSE),"Not found").
  4. Use "" instead of "Not found" to show a blank cell, or 0 if later SUM formulas need a number.
  5. Prefer IFNA over IFERROR. IFERROR also hides #REF!, #VALUE! and #DIV/0!, which usually point to real problems.

6. Check array formulas, NA() cells and custom functions

  1. Trace the #N/A to its origin with Formulas > Error Checking > Trace Error, which draws arrows to the cell where the error starts.
  2. If the source cell contains =NA() or a typed #N/A, it is a placeholder. Replace it with the real data once you have it.
  3. If the formula shows curly braces { } in the formula bar (legacy array formula), make sure every range it references has the same number of rows and columns as the range the formula fills. Re-enter it with Ctrl+Shift+Enter, or just Enter in Microsoft 365.
  4. If the formula uses a custom function, open the workbook or enable the add-in that contains it. Check the code with Alt+F11 (Windows) or Tools > Macro > Visual Basic Editor (Mac).

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Pick one failing row and test the pieces separately. Put =A2=D5 next to the lookup value and the entry you expect it to match. If that returns FALSE, compare =LEN() and =CODE(LEFT(A2,1)) for both cells to find the hidden difference. You can also select the formula cell and use Formulas > Evaluate Formula (Windows) to step through the calculation. If the data comes from an export, Power Query or an external system, the fix may belong at the source, so ask whoever owns that report whether IDs are sent as text or as numbers. For a workbook shared through your organisation, your IT or data team can check for missing add-ins. You can also post the formula and a sample of both columns on Microsoft Q&A or the Microsoft Tech Community Excel forum.

Questions people ask

Why does VLOOKUP return #N/A when the value is clearly there?

The two values look the same but aren't identical to Excel. Usually one is a number stored as text or has a trailing or non-breaking space. Test with =A2=D5, then use Convert to Number or TRIM to make both sides match.

How do I replace #N/A with a blank or 0?

Wrap the formula in IFNA, for example =IFNA(VLOOKUP(A2,D:F,3,FALSE),"") for a blank or ,0) for zero. In XLOOKUP, put the replacement in the fourth argument: =XLOOKUP(A2,D:D,F:F,"").

What is the difference between IFNA and IFERROR?

IFNA catches only #N/A, which is normally what you want for lookups. IFERROR catches every error type, including #REF! and #VALUE!, so it can hide real mistakes in the formula.

How do I SUM a column that contains #N/A values?

SUM returns #N/A if any cell in the range is #N/A. Use =AGGREGATE(9,6,B2:B100), which ignores error values, or =SUMIF(B2:B100,"<>#N/A"). Better still, fix the lookup formulas with IFNA so they return 0.

Does #N/A mean my formula is broken?

Not necessarily. It often means the formula works correctly and the value simply isn't in the lookup list. If some rows return results and others return #N/A, check whether those specific values exist and match exactly.

Sources

Related errors