IFNA

What it does

IFNA works like IFERROR but catches only #N/A, the error lookups give when they find nothing. Other errors still show, so real problems stay visible.

Syntax

=IFNA(value, value_if_na)

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

value

The formula that might return #N/A, usually a lookup.

value_if_na

What to show when it does.

Example

Look up the price of code P40, which isn't in the list.

The formula in E2:

=IFNA(VLOOKUP(D2,A2:B4,2,FALSE),"not found")

Result: not found

P40 isn't in A2:A4, so VLOOKUP returns #N/A and IFNA swaps it for "not found".

Common mistakes

• Expecting it to hide every error. A column number that's too big gives #REF!, which IFNA lets through.
• Wrapping the wrong part. Put the whole lookup inside IFNA, not just the value it looks for.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• When a lookup finds nothingModule 5 · Lookups

More from this module

• VLOOKUP
• HLOOKUP
• XLOOKUP

All functions