Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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

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

share|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
    
+1 for both the question & answer. Thanks Both. –  Veynom Sep 23 '13 at 16:19

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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