If the result of any formula is an error, you can replace it with text, numbers, or even another formula, using IFERROR function in Excel.
Basically, you need to tell Excel – If this formula results in an error, do this!
IFERROR works with any function, like Vlookup, IF, etc. and also with any kind of error.
Click here to know the kind of errors in Excel.
To use IFERROR, just put this function before the Vlookup or IF or any other function.
At the end of the Vlookup, put a comma , and then add whatever you need as explained in the examples below.
=IFERROR ( Vloopkup (____________), xxxxxx)
xxxxxx is what you want to be shown/done when the Vlookup results in an error.
Taking an example of a Vloopkup formula.
There are 10 employees in a company. Based on a lucky draw, 3 of the employees A01, A05 and A07 get extra incentives of $1000, $500 and $350 respectively.
=Vlookup formula is used to find against each employee code, what’s the incentive based on the data on the right-side as shown below.
Remaining 7 employees do not get any incentive, thus a #N/A error appears as the result of the formula.
You do not want #N/A to appear, instead,
(Add =IFERROR function before the Vlookup function as below in each case).
- You want the text “No incentive”.
- You want the amount of $50 to be given to all others.
- You want to give each of them 0.1% of their annual salary as incentive.
Only A01, A05 and A07 have got the lucky draw incentives, else everyone has got based on the formula of IERROR’s result.