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.