One of the most common questions is how to combine IFERROR and VLOOKUP to improve the results that VLOOKUP generates. VLOOKUP is a great formula where the error message of #N/A is actually useful and tells you something about your spreadsheet (learn more about VLOOKUP through the online VLOOKUP course). The problem is that these error messages can affect other calculations in the spreadsheet.
So in the example below we are trying to match student numbers to a list of outstanding bills. As shown some students are matched, but where there is no match it returns a #N/A. This is OK because it means that Excel couldn’t find that student in the list, but the problem is that when we try and add up the total bill outstanding (in cell B32) is also gives an #N/A error.
In order to stop this, we can use the IFERROR function and tell Excel that IF it finds an ERROR, it means that the student is not on the list and we want to show a zero as they owe nothing.
The IFERROR syntax is very easy i.e.
=IFERROR( the formula , what you want to see if it is an error )
The best way to build it is to first build your VLOOKUP formula and check that it is working correctly. Then, and only then, should you wrap the IFERROR function around it.
As shown below you can add an ‘IFERROR(‘ just after the = sign and then at the end of the formula type ‘ , 0 )’.
The end result will be a zero wherever it can’t find a match, and the actual amount where it can. It will then be possible to add it up!
Suggestions on nesting a VLOOKUP inside an IFERROR
Whenever you want to use a IFERROR, please remember to do it in this order.
- Build the VLOOKUP or other formula first, and make sure it makes sense. Remember to test one of the cells down and/or to the right of the original formula (the original formula will almost always be correct, but it is when you copy that things go wrong)
- Wrap the IFERROR around the VLOOKUP.
Related
Difference between IFERROR and ISERROR in Excel
Vlookup not finding a value that is there (when using Find)
Using VLOOKUP Exact match (video)