Hiding #N/A with IFNA
Look up the price of each order in D2:D6. Two items aren't on the price list any more.
keyboard 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.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.