Quick replace #N/A by 0 when vlookup

Question

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 1

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

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

Answer 2

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.

Answer 3

This is actually pretty powerful, and I've been scouring the web for a while to get this question answered. In my case, however, I don't want to replace the #N/A with a 0, but instead, I want to use alternate information. In my case, if the VLOOKUP doesn't find anything, I want it to use the contents of another cell. (I'm using Excel 2010, but that shouldn't matter) Assume that my formula in cell E3 on Sheet1, and the data I want to pull is somewhere on Sheet2. If there is no data to be found on Sheet2, then I want my cell to use data from Sheet1.

=IFERROR(VLOOKUP(A3,Sheet2!B2:E3,4,FALSE),Sheet1!D3)

To the original responder, THANK YOU. You made my life a bit easier tonight. I hope my rambling expansion on your answer helps someone, too!