formulastreak

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

#N/A

The formula couldn't find what it was asked to look for.

#N/A means “not available”. It nearly always comes from a lookup (VLOOKUP, XLOOKUP, MATCH, INDEX/MATCH) whose search value isn't in the table. Sometimes that's expected (a new product with no price yet); often it's a tiny mismatch you can't see.

Common causes, ranked

  1. 1

    The value really isn't in the lookup range

    A typo, a code that was never added, or a range that stops one row too early.

    Fix: Check the value exists in the first column (VLOOKUP) or the lookup range (XLOOKUP). Lock the table with $ so it doesn't slide when you fill the formula down.

    Shows the error

    =VLOOKUP(B2,G2:J8,2,FALSE)

    Fixed

    =VLOOKUP(B2,$G$2:$J$8,2,FALSE)
  2. 2

    Numbers stored as text (or the other way round)

    1042 the number and "1042" the text look identical but never match. Imports and copied data often store numbers as text.

    Fix: Make both sides the same type. Convert text to numbers with VALUE, or format the cells as numbers and re-enter them.

    Shows the error

    =VLOOKUP(A2,$G$2:$H$9,2,FALSE)

    Fixed

    =VLOOKUP(VALUE(A2),$G$2:$H$9,2,FALSE)
  3. 3

    Extra spaces

    "Mara " with a trailing space isn't "Mara". Spaces are invisible, so the lookup looks right but fails.

    Fix: Clean the value with TRIM (or clean the column once and paste the values back).

    Shows the error

    =XLOOKUP(A2,$F$2:$F$9,$G$2:$G$9)

    Fixed

    =XLOOKUP(TRIM(A2),$F$2:$F$9,$G$2:$G$9)
  4. 4

    Approximate match where you needed an exact one

    VLOOKUP's last argument defaults to approximate match. On unsorted data it can give #N/A or, worse, a wrong row. MATCH behaves the same without its 0.

    Fix: Add FALSE as VLOOKUP's last argument, or 0 as MATCH's match type.

    Shows the error

    =MATCH(D2,$A$2:$A$9)

    Fixed

    =MATCH(D2,$A$2:$A$9,0)
  5. 5

    #N/A typed in, or NA() used as a placeholder

    Someone marked missing data with #N/A or =NA(), and every formula that reads it shows #N/A too.

    Fix: Replace the placeholder with the real data once you have it.

See it live

Edit the formula in D2 and fix the error yourself.

PL-999 isn't in the price list, so D9 shows #N/A · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = Monstera
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042MonsteraPL-101Fern$9.5040
32002PT-2101Terracotta potPL-104Monstera$34.0012
42003TL-3013Watering canPL-107Snake plant$22.0025
52004PL-1011FernPT-210Terracotta pot$12.5060
62005SL-4014Potting mixPT-212Ceramic pot$19.0018
72006PL-1041MonsteraTL-301Watering can$18.009
82007PT-2122Ceramic potSL-401Potting mix$7.2580
92008PL-9991#N/A

How to hide it safely

When “not found” is a normal outcome, catch only #N/A with IFNA, or use XLOOKUP's own if_not_found argument:

=IFNA(VLOOKUP(B2,$G$2:$J$8,2,FALSE),"Not found")

Careful: Hide it only after you know why it happens. IFNA is safer than IFERROR here: it still shows other errors, such as #REF! from a broken range, instead of covering them with “Not found”.

Excel vs Google Sheets

#N/A works the same way in both. Google Sheets' FILTER also returns #N/A when nothing matches (Excel's shows #CALC!). IFNA is in Excel 2013 and later and in Google Sheets. In Excel, IFERROR needs both arguments; in Google Sheets the second is optional and leaves the cell blank when you skip it.

Sources

Practise this in 5 minutes

Fixing #N/A is taught in Lesson 15: VLOOKUP (Unit 4, Lookups), on a live sheet with instant feedback. Pro

Open “VLOOKUP”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.