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
Search JabSto ::




Custom Search