microsoft excel - Quick replace #N/A by 0 when vlookup

Friday, 2 November 2018

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?

Answer

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

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

Notes