22

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?

33

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

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

| improve this answer | |
  • correctomundo +1 – Raystafarian Mar 5 '12 at 15:55
  • @Raystafarian Cannot get what you mean :) – Nam G VU Mar 6 '12 at 10:19
  • @barry houdini Horay! – Nam G VU Mar 6 '12 at 10:19
  • @NamGiVU I was just agreeing with barry's answer :) – Raystafarian Mar 6 '12 at 12:36
5

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.

| improve this answer | |
1

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!

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.