To hide the #N/A error that VLOOKUP throws when it can’t find a value, you can use the IFERROR function to catch the error and return any value you like. When VLOOKUP can’t find a value in a lookup table, it returns the #N/A error. The IFERROR function allows you to catch errors and return your own custom value when there is an error. If VLOOKUP returns a value normally, there is no error and the looked up value is returned. If VLOOKUP returns the #N/A error, IFERROR takes over and returns the value you supply.
Fixing VLookup errors using IFERROR will take few steps:
- While writing lookup formulas like VLOOKUP, INDEX+MATCH it is common to search for values that do not exist in your data. You can wrap such formulas in IFERROR for peace of mind.
Ex: =IFERROR(VLOOKUP(…),”Not found”)
- While using reference formulas like INDEX, OFFSET, frequently, we try to fetch the data that is not in the list of values. This returns #REF errors. You can fix them with IFERROR easily.
- While using arithmetic, numeric expressions, usually we end up dividing by 0. You can fix such things by using IFERROR.
Ex: =IFERROR(AVERAGE(…),”0″) — Returns 0 when the list has zero values.