If you want to hide the #N/A error that VLOOKUP throws when it can’t find a value, you use the IFERROR function to catch the error and return any value you like.
How the formula works
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 value is returned. If VLOOKUP returns the #N/A error, then IFERROR takes over and returns the value you supply.
If you have a lookup value in cell A1 and lookup values in a range named table, and you want a cell to be blank if no lookup is found, you can use:
If you want to return the message “Not found” when no match is found, use:
In earlier versions of Excel that lack the IFERROR function, you’ll need to repeat the VLOOKUP inside the IF function:
