VLOOKUP

Posted by keithfletcher on January 11, 2001 8:53 AM

Yes, I am looking to create a vlookup formula where if the number I am looking up is not in the list rather then return "#n/a" I want it to read "0" or any other value that I desire. How can I do it where this would occur? Thanks for any assistance.

Posted by Mark W. on January 11, 2001 9:04 AM

Why?

Posted by lenze on January 11, 2001 10:30 AM

=IF(ISERROR(VLOOKUP(your formula)),0,VLOOKUP(your formula))

Posted by Mark W. on January 11, 2001 11:16 AM

Your much better off handling the error condition
in the formulas that reference the VLOOKUP() results.

=IF(VLOOKUP(...),0,VLOOKUP(...)) is VERY
ineffecient!!!

Posted by Marianne on January 11, 2001 11:17 AM

I tried this and it didn't work for me. Keith, I am wondering if this worked for you. If not, I would love to know how to do this also. Those "#n/a"'s bug the heck out of me.

Marianne

Posted by Mark W. on January 11, 2001 12:33 PM

You're much better off handling the error in other
ways, such as:

=SUMIF(ref,"&LT;&GT;#N/A")
{=SUM(IF(ISNA(ref),0,ref))}

Posted by Marianne on January 11, 2001 2:00 PM

This is what I did in my worksheet:

=IF(A4="",0,VLOOKUP...))

Note in my worksheet A4 the cell that tells the lookup formula what to look up. I get the #N/A when that cell is blank. Thus, I used A4="".

I don't know if Mark will agree with this, but it seems to work for me. Thanks for asking the question.

Marianne

Posted by Mark W. on January 11, 2001 2:23 PM

...but, this alone won't guarantee the absence of
#N/A errors which are caused by:

1. A lookup value that doesn't exist in the left-
most column of your table. Your lookup value maybe
a value other than "" and still not be in your
lookup table. For example, =VLOOKUP("a",{"b","c"},1)

2. The lookup table isn't sorted properly (if the
4th argument &LT;&GT; 0). For example,
=VLOOKUP("a",{"b","a"},1,1).

Posted by keithfletcher on January 11, 2001 3:02 PM

thanks, i tried that formula in lenze's reply and it did the trick. thank you and thank you all others who responded. i wish i would have found this message board or thought of it awhile ago. thanks again.

Posted by Aladin Akyurek on January 11, 2001 6:31 PM

While I agree to a great degree with your stipulation (that is, handling errors during further processing instead of disguising them), I think it's equally if not more important to emphasize the control of input (e.g., by Data Validation) that undergoes further proccessing.

Aladin

Posted by Marianne on January 11, 2001 7:34 PM

I agree with you completely and good point.

Thanks for explaining that further.

Marianne