Excel Practice
Lessons Lesson 49

IFNA and the Not-Found Value

About this lesson

IFNA returns a calculation's result, unless that result is #N/A. Then it returns the value you gave instead. Every other kind of error stays visible.

By the end you can

  • replace a lookup's #N/A with words using IFNA.
  • say why IFNA is safer than IFERROR around a lookup.
  • use XLOOKUP's built-in not-found argument instead.

Practises IFNA IFERROR VLOOKUP XLOOKUP

The idea

#N/A is the one error that is often the truth. The lookup looked, and the value is not there. IFNA replaces exactly that with something a reader can act on: Not listed, Unknown, 0. It lets every other error through. A #REF! from a wrong column number still shows. That one is a mistake in the formula, not a fact about the data.

Prefer IFNA to IFERROR around a lookup for that reason. And when XLOOKUP is available, its fourth argument does the same job without a wrapper at all.

The mistake to watch for

A fallback of 0 on a price. It is convenient for the total and dangerous on an invoice, because a missing price now looks free. Choose the not-found value for the person reading the sheet. And prefer IFNA to IFERROR around a lookup, so that a broken column number still shows as the error it is.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, look up the price of the code in D2 from the list with VLOOKUP, exact match.

To begin, type it exactly:

=VLOOKUP(D2,A2:B5,2,FALSE)

E2
Row A B C D E F G
1 Code Price Ordered Price
2 P100 4.5 P200
3 P200 7.25 P900
4 P300 12 P400
5 P400 3.1 P350
6
7
8

Every step

  1. 1

    In E2, look up the price of the code in D2 from the list with VLOOKUP, exact match. To begin, type it exactly: =VLOOKUP(D2,A2:B5,2,FALSE).

    Hint. Column 2 of A2:B5, exact match.

  2. 2

    E3 looks up the code in D3. P900 is not on the price list, so it shows #N/A. Wrap it in IFNA so the cell reads Not listed instead.

    Hint. Same shape as IFERROR, narrower net.

  3. 3

    In E4, the same lookup for D4 with an IFNA fallback of 0, so the column can be totalled.

    Hint. Lookup inside, 0 after the comma.

  4. 4

    E5 holds =IFNA(VLOOKUP(D5,A2:B5,2,FALSE),0) and D5 is P350, which is not on the list. What does E5 show?

    Hint. Look for P350 in the list.

  5. 5

    XLOOKUP has the fallback built in. In F3, write a formula using XLOOKUP, with no IFNA. It should give the price of the code in D3, or the text Not listed.

    Hint. Fourth argument, in quotation marks.