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

By Ali, Founder, Excel Lessons

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.

Read more about this

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, type:

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

That is the price of the code in D2, exact match.

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, type: =VLOOKUP(D2,A2:B5,2,FALSE). That is the price of the code in D2, exact match.

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

  2. 2

    E3 shows #N/A because P900 is not on the price list. Fix E3. Wrap the lookup in IFNA so the cell shows Not listed. Remember: IFNA catches only #N/A, so every other error stays visible.

    Hint. Same shape as IFERROR, narrower net.

  3. 3

    In E4, write the lookup for D4 with an IFNA fallback of 0. Shape: =IFNA(lookup, value_if_na). Then the column can be totalled.

    Hint. Lookup inside, 0 after the comma.

  4. 4

    D5 is P350, which is not on the list. Read =IFNA(VLOOKUP(D5,A2:B5,2,FALSE),0) and say what E5 shows.

    Hint. Look for P350 in the list.

  5. 5

    XLOOKUP has the fallback built in. In F3, look up the price for the code in D3 with XLOOKUP. Shape: =XLOOKUP(lookup, lookup_range, return_range, if_not_found). With no IFNA, show Not listed when the code is missing.

    Hint. Fourth argument, in quotation marks.