Hiding #N/A with IFNA
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.
Find the price of each ordered item in E2:E6. Some items are no longer sold: show "Not found" for those instead of #N/A.
Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Order | Price | ||
| 2 | Monitor Stand | 52.99 | Wireless Mouse | |||
| 3 | Headset | 49.25 | Gel Pens (10) | |||
| 4 | Office Chair | 171.25 | Paper (500) | |||
| 5 | Bookshelf | 83.25 | Notebook A5 | |||
| 6 | USB-C Hub | 35.25 | Monitor Stand | |||
| 7 | Notebook A5 | 5.99 | ||||
| 8 | Filing Cabinet | 182.49 | ||||
| 9 | Desk Organiser | 13.5 | ||||
| 10 | Whiteboard | 43.49 | ||||
| 11 | Wireless Mouse | 32.5 | ||||
| 12 | ||||||
| 13 | ||||||
| 14 | ||||||
| 15 |
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
SUMof that column shows #N/A too.What does
IFNAdo?IFNAchecks 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?IFERRORcatches 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.IFNAonly catches "not found", so real mistakes still show up.VLOOKUPnot 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
XLOOKUPneedIFNA?No.
XLOOKUPhas 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.