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


  • correctomundo +1 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 :) Mar 6 '12 at 12:36

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


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


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.


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!

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