Excel errors / #N/A

#N/A error in Excel

A lookup could not find the value it was given.

#N/A means "not available". A formula went looking for a value and did not find it. It comes almost always from a lookup: VLOOKUP, XLOOKUP, HLOOKUP or MATCH.

The formula itself is usually fine. What is wrong is the match: the value is missing from the list, or it is there but not quite the same as far as Excel is concerned. A space at the end, or a number stored as text, is enough.

Have a formula that shows #N/A? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.

5 causes of #N/A, and how to fix each

  1. The value is not in the list

    The simplest case: the code, name or ID you are looking up has no row in the table. Often that is correct, such as a new product that has no price yet.

    ABCD
    1CodePrice
    2P-13.5#N/A
    3P-21.2

    In D2: =VLOOKUP("P-9", A2:B3, 2, FALSE) → #N/A

    How to fix it. Add the missing row if it should be there. If a missing value is normal, wrap the lookup in IFNA and say what to show instead.

    ABCD
    1CodePrice
    2P-13.5Not found
    3P-21.2

    In D2: =IFNA(VLOOKUP("P-9", A2:B3, 2, FALSE), "Not found") → Not found

  2. A space you cannot see

    Data copied from another system often carries a space at the end. "P-1 " and "P-1" look the same in the cell and are different to Excel. In the sheet below, A2 has such a space.

    ABCD
    1CodePrice
    2P-1 3.5#N/A
    3P-21.2

    In D2: =VLOOKUP("P-1", A2:B3, 2, FALSE) → #N/A

    How to fix it. Remove the spaces in the data (a helper column with TRIM, then paste it back as values). Or clean the column inside the formula: XLOOKUP accepts TRIM(A2:A3) as the range to search.

    ABCD
    1CodePrice
    2P-1 3.53.5
    3P-21.2

    In D2: =XLOOKUP("P-1", TRIM(A2:A3), B2:B3) → 3.5

  3. A number on one side, text on the other

    The ID 1002 typed as a number does not match "1002" stored as text, which is common in exported files. Numbers stored as text sit on the left of the cell, and often carry a small green triangle.

    ABCD
    1IDName
    2'1001Ana#N/A
    3'1002Ben

    In D2: =VLOOKUP(1002, A2:B3, 2, FALSE) → #N/A

    How to fix it. Make both sides the same kind. To turn a whole column of text into numbers, select it and use Data > Text to Columns > Finish. Or convert the value you look up: TEXT(1002, "0") makes it text.

    ABCD
    1IDName
    2'1001AnaBen
    3'1002Ben

    In D2: =VLOOKUP(TEXT(1002, "0"), A2:B3, 2, FALSE) → Ben

  4. VLOOKUP is searching the wrong column

    VLOOKUP only looks in the first column of the table you give it. If the value you search for is in the second column, it is never found, even though you can see it.

    ABCD
    1NameCode
    2AnaP-1#N/A
    3BenP-2

    In D2: =VLOOKUP("P-2", A2:B3, 1, FALSE) → #N/A

    How to fix it. Use XLOOKUP, which takes the column to search and the column to return separately, or INDEX with MATCH.

    ABCD
    1NameCode
    2AnaP-1Ben
    3BenP-2

    In D2: =XLOOKUP("P-2", B2:B3, A2:A3) → Ben

  5. The table moved when the formula was filled down

    A lookup range written as A2:B50 shifts to A3:B51, then A4:B52, as the formula is copied down. Rows further down search a table that no longer includes the top rows, so values near the top stop being found.

    How to fix it. Lock the range with $ signs before filling down: $A$2:$B$50. Select the range in the formula and press F4.

Hiding it on purpose

To show your own text in place of #N/A, use IFNA: =IFNA(VLOOKUP(E2, A2:B50, 2, FALSE), "Not found"). IFERROR works too, but it also hides every other error, including a misspelled function name. XLOOKUP has this built in as its fourth argument.

Common questions

How do I fix #N/A in VLOOKUP?

Check, in this order: that the value really is in the first column of the table; that neither side has extra spaces; that both are numbers or both are text; that the formula ends with FALSE for an exact match; and that the table range is locked with $ signs.

Why does VLOOKUP return #N/A when the value exists?

Because to Excel it is not the same value. The usual reasons are a space at the end of one of them, or a number in one place and the same digits stored as text in the other. =A2=E2 in an empty cell tells you: FALSE means they differ.

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

Wrap the formula in IFNA: =IFNA(VLOOKUP(E2, A2:B50, 2, FALSE), 0) for zero, or "" in place of the 0 for an empty-looking cell.

Practise it

Functions involved

Other errors