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?
3 Answers
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.
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!
