#excel iferror

Our VLOOKUP tutorial offers a fast way to get started with Excel’s VLOOKUP and IFERROR functions.

New Post has been published on http://access-excel.tips/excel-iferror-function/

Excel IFERROR Function

This Excel tutorial explains how to use Excel IFERROR function for worksheet. IFERROR can be used with vlookup to capture ISNA error.

You may also want to read:

Check Excel #N/A using ISNA Function

What is Excel IFERROR Function?

Excel IFERROR Function is an error handling function, if an “Error” is found, then a desired value is returned.  This is a new function introduced in Excel 2007, which means Excel 2003 and prior versions cannot be used.

Syntax of IFERROR

IFERROR(value,value_if_error)

The types of errors that can be captured are shown in the below table.

#N/A #VALUE! #REF! #DIV/0! #NUM! #NAME? #NULL!

Example of Excel IFERROR

One common use of IFERROR function is to use with Vlookup. If a lookup value is not found, Vlookup function returns #N/A

In Excel 2003, we used to use ISNA or ISERROR to capture the error.

=IF(ISNA(VLOOKUP(xxxx)),"Not found",VLOOKUP(xxxx)) OR =IF(ISERROR(VLOOKUP(xxxx)),"Not found",VLOOKUP(xxxx))

In Excel 2007 and newer version, because IFERROR has combined functions “IF” and “ISERROR”, we can simplify the formula as

=IFERROR(VLOOKUP(xxxx),”Not found”)

Although many people use IFERROR nowadays, you should be very careful that you will also turn other errors, not just those values you fail to lookup.

For example, we tried to look up Employee ID from the below source table.

Employee ID Formula Result 1 =IFERROR(VLOOKUP(D3,$A$3:$B$7,2,0),”Not found”) Mary 6 =IFERROR(VLOOKUP(D4,$A$3:$B$7,2,0),”Not found”) Not found 7 =IF(ISNA(VLOOKUP(D5,$A$3:$B$7,2,0)),”Not found”,VLOOKUP(D5,$A$3:$B$7,2,0)) Not found 8 =IF(ISERROR(VLOOKUP(D5,$A$3:$B$7,2,0)),”Not found”,VLOOKUP(D5,$A$3:$B$7,2,0)) Not found

Outbound References

https://support.office.com/en-US/Article/IFERROR-function-f59bacdc-78bd-4924-91df-a869d0b08cd5?ui=en-US&rs=en-US&ad=US