VLOOKUP

Mike · Feb 25th, 2002, 02:17 PM

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 · Feb 25th, 2002, 02:35 PM

Quote:

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 · Feb 25th, 2002, 02:36 PM

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

anno · Feb 25th, 2002, 04:07 PM

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 · Jul 2nd, 2002, 02:12 PM

=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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 02:17 PM	#1
Mike New Member Join Date: Feb 2002 Posts: 5	I've seen it before How do I not return a #NA in a vlookup statement? Please direct me to the archived answer

Feb 25th, 2002, 02:36 PM	#3
RichardS Board Regular Join Date: Feb 2002 Location: Victoria, Australia Posts: 761	=IF(ISNA(VLOOKUP(BLAH BLAH BLAH)"",VLOOKUP(BLAH BLAH BLAH))

Feb 25th, 2002, 04:07 PM	#4
anno Board Regular Join Date: Feb 2002 Posts: 202	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 ]

Jul 2nd, 2002, 02:12 PM	#5
mapalumbo New Member Join Date: Jun 2002 Posts: 4	=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