Excel track · Exercise 67 of 86 Paid

IFNA: catch only a missing match

Replace a missing lookup match and leave every other error visible.

Advanced

Code and price table with one code the table does not hold

Read-only data · screen 1 of 2 · answer in B9

In B9, look the code in A8 up in the table and say not on the list when it is missing.

	A	B
1	Code	Price
2	A-1	10
3	B-2	20
4	C-3	30
5	A-1	12
6	B-2	22
7	C-3	32
8	Q-9
9	Missing code

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In B9, price the code in A2 with the same lookup, keeping the fallback for a missing code.

	A	B
1	Code	Price
2	A-1	10
3	B-2	20
4	C-3	30
5	A-1	12
6	B-2	22
7	C-3	32
8	Q-9
9	Found code

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

IFNA evaluates its first argument and returns the second only when that argument fails with #N/A. A lookup that finds nothing raises exactly that error, which makes IFNA the narrow net around a missing code. Every other error passes through untouched.

Here Q-9 sits in A8, and the code column runs A-1, B-2, C-3, then the same three again. A lookup that demands an exact match finds nothing and prints not on the list. The second screen asks about A-1, which is in A2 at 10.

An approximate match returns 32 for Q-9 and never reaches the fallback, because a near miss counts as a match. Set the fourth argument to FALSE. Keep the guard on the second screen too, where the code is found, because the same formula gets copied to rows that miss.

Syntax

=IFNA(value, value_if_na) replaces #N/A and nothing else

Worked example

On Code and price table with one code the table does not hold, =IFNA(VLOOKUP(A8,A2:B7,2,FALSE),"not on the list") returns not on the list.

=IFNA(VLOOKUP(A8,A2:B7,2,FALSE),"not on the list")

Common errors

Error codes for IFNA around VLOOKUP, XLOOKUP and INDEX/MATCH, and which errors it leaves alone
Error	What it means and how to fix it
`#N/A`	The lookup matched nothing; without the guard this is the error B9 would hold.
`#NAME?`	The function name is typed as IFNAA or IFA, and Excel reads what is there without guessing.
`Wrong price`	The fourth argument is set to TRUE or left out, so an approximate match answers 32 for Q-9 and the fallback never fires.

At work

Pricing an order line from a product list, where a code nobody stocks should say so instead of showing #N/A.