VLOOKUP without #N/A error

An excel formula to vlookup without #n/a error

Related Functions

Sponsored Link


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:

=IFERROR(VLOOKUP(A1,table,2,FALSE),"Not found")

In earlier versions of Excel that lack the IFERROR function, you'll need to repeat the VLOOKUP inside the IF function:


Sponsored Link

0 votes. 0 / 5