VLOOKUP

[Mike]

I've seen it before
How do I not return a #NA in a vlookup statement? Please direct me to the archived answer

 

[Aladin Akyurek]

On 2002-02-25 13:17, Mike wrote:
I've seen it before
How do I not return a #NA in a vlookup statement? Please direct me to the archived answer

Mike,

Would take eons before I find one of the many VLOOKUP formulas, whence...

If you need an exact match, use:

=IF(COUNTIF(A1:A7,lookup-value),VLOOKUP(lookup-value,A1:D7,2,0),0)

Otherwise use:

=IF(ISNUMBER(MATCH(lookup-value,A1:A7)),VLOOKUP(lookup-value,A1:D7,2),0)

Both return 0 instead of #N/A.

A1:D7 is the lookup-table, whose first column is A1:A7.

Aladin

 

[RichardS]

=IF(ISNA(VLOOKUP(BLAH BLAH BLAH)"",VLOOKUP(BLAH BLAH BLAH))

 

[anno]

if it's always the same value (eg. "0") that causes the #N/A could you add it to your lookup table? an example of what i mean is at http://www.mrexcel.com/wwwboard/messages/21090.html and responses
This message was edited by anno on 2002-02-25 15:08

 

[mapalumbo]

=IF(ISERROR(VLOOKUP(a1,$b$7:$C$12,2,0)=TRUE),"no error",VLOOKUP(a1,$b$7:$C$12,2,0))

Try this above.

Best of luck