IFNA

Hiding #N/A with IFNA

Spreadsheet in your browser
Lesson concept What you'll do
1 / 7

Show a friendly message when a lookup finds nothing.

Wrap the lookup in IFNA (or IFERROR) and give the message to show instead of #N/A.

Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEF
1ProductPriceOrderPrice
2Monitor Stand52.99Wireless Mouse
3Headset49.25Gel Pens (10)
4Office Chair171.25Paper (500)
5Bookshelf83.25Notebook A5
6USB-C Hub35.25Monitor Stand
7Notebook A55.99
8Filing Cabinet182.49
9Desk Organiser13.5
10Whiteboard43.49
11Wireless Mouse32.5
12
13
14
15
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How IFNA and IFERROR works

  • Why does a lookup show #N/A?

    #N/A means "not available": the value you looked for isn't in the table. It is the right answer, but a column full of #N/A looks like a broken sheet, and a SUM of that column shows #N/A too.

  • What does IFNA do?

    IFNA checks a formula's result. If it is #N/A, it shows something else instead. Otherwise it shows the result as normal:

    =IFNA(VLOOKUP(D2,$A$2:$B$11,2,FALSE),"Not found")

  • Why not IFERROR?

    IFERROR catches every error, not just #N/A. If you typed the wrong column number or deleted part of the table, it hides that mistake too, and you never find out. IFNA only catches "not found", so real mistakes still show up. VLOOKUP not working shows how to find the cause before you hide it.

  • What can go in the second part?

    Any value: text in quotes such as "Not found", a blank "", or a 0 so the column still adds up.

  • Does XLOOKUP need IFNA?

    No. XLOOKUP has a fourth part for exactly this:

    =XLOOKUP(D2,$A$2:$A$11,$B$2:$B$11,"Not found")

Know someone fighting with a spreadsheet? Send them this page.