Microsoft Office Tutorials and References

In Depth Information

**Syntax**

=IF(ISNA(VLOOKUP(B7,$F$2:$G$9,2,FALSE)),"New Rep",

VLOOKUP(B7,$F$2:$G$9,2,FALSE))

In English, this formula says to first find the rep name in the lookup table.

If the rep is not found and returns the #N/A error, then use some other text,

which in this case is the words NewRep. If the rep is found, then perform the

lookup again and use that result.

Because VLOOKUP was one of the most time-intensive functions, it was hor-

rible to have Excel perform every VLOOKUP twice in this formula. In a data

set with 50,000 records, it could take minutes for the VLOOKUP to complete.

Microsoft wisely added the new IFERROR function to handle all these error-

checking situations.

Starting in Excel 2013, Microsoft has added the IFNA function. It works just

like the IFERROR function, but the second argument is only used when the

first argument results in an #N/A error. You might be able to imagine a situ-

ation where you want to replace the #N/A errors but allow other errors to

appear.

Syntax

IFERROR(value,value_if_error)

The advantage of the IFERROR function is that the calculation is evaluated

only once. If the calculation results in any type of an error value, such

as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!, Excel returns

the alternative value. If the calculation results in any other valid value,

whether it is numeric, logical, or text, Excel returns the calculated value.

Syntax

IFNA(value,value_if_na)

If the expression evaluates to a value of #N/A, then IFNA returns

value_if_nainstead of the expression. Added in Excel 2013, this function only

replaces #N/A errors and allows other errors to appear as the result.

The formula from the preceding section can be rewritten as

=IFERROR(VLOOKUP(B7, $F$2:$G$9,2,FALSE),"New Rep") or as

=IFNA(VLOOKUP(B7, $F$2:$G$9,2,FALSE),"New Rep") (see
Figure 12.9
)
. Al-

though IFNA is a bit shorter than IFERROR, the new IFNA function fails for

anyone using Excel 2010 or earlier. This makes IFERROR a safer function to