Quick replace #N/A by 0 when vlookup

I use vlookup a lot in excel.

The problem is with #N/A value when the seek value is not found.

In that case, we often replace it by 0 using

if(isna(vlookup(what,range,column,false));0; vlookup(what,range,column;false))

which repeat vlookup(what,range,column,false) twice and make the formula look ugly & dummy to me.

Do you have other work around for this issue?


Which version of Excel? In Excel 2007 or later you can use IFERROR function like this

=IFERROR(VLOOKUP(A1,B2:E3,4,0),0)


You can also use IFNA if you only want to detect #N/A errors.

=IFNA(Formula,0)

This will display 0 instead of the #N/A error.