When Using VLOOKUP With MATCH How Do I Handle #NA On Data Retrieval?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Katlyn Jones - 31 Jan 2008 18:11 GMT

I am using =VLOOKUP($A$32,Data!$A$4:$M$18,MATCH($B$3,Data!$B$4:$M$4,0)+1,0) in order to search for two dimensions in a table. I am a very green user so do not know much in the way of Excel.

The problem is when there is no matching field I receive a #NA. How do I just get a blank or 0 if there is no matching data?

Thank you in advance for your help.

A Womand Told Me
http://www.awomantoldme.com

Reply to this Message

JP - 31 Jan 2008 18:18 GMT

Hate to do this, but....wrap your formula in an IF function that uses
ISNA. If ISNA evaluates to TRUE, it will display nothing.

=IF(ISNA(VLOOKUP($A$32,Data!$A$4:$M$18,MATCH($B$3,Data!$B$4:$M
$4,0)+1,0)),"",VLOOKUP($A$32,Data!$A$4:$M$18,MATCH($B$3,Data!$B$4:$M
$4,0)+1,0))

HTH,
JP

> I am using =VLOOKUP($A$32,Data!$A$4:$M$18,MATCH($B$3,Data!$B$4:$M$4,0)+1,0) in order to search for two dimensions in a table. I am a very green user so do not know much in the way of Excel.
>
[quoted text clipped - 3 lines]
>
> A Womand Told Mehttp://www.awomantoldme.com

Reply to this Message

Harlan Grove - 31 Jan 2008 20:08 GMT

JP <jp2...@earthlink.net> wrote...

>Hate to do this, but....wrap your formula in an IF function that
>uses ISNA. If ISNA evaluates to TRUE, it will display nothing.
>
>=IF(ISNA(VLOOKUP($A$32,Data!$A$4:$M$18,
>MATCH($B$3,Data!$B$4:$M$4,0)+1,0)),"",
>VLOOKUP($A$32,Data!$A$4:$M$18,MATCH($B$3,Data!$B$4:$M$4,0)+1,0))

...

There are 3 ways the VLOOKUP call could return #N/A: A32 doesn't occur
in A4:A18 (or is #N/A itself), B3 doesn't occur in B4:M4 (or is #N/A
itself), or the cell corresponding to A32 and B3 happens to evaluate
to #N/A. The last should propagate in most situations. It's possible
to handle the first two with

=IF(COUNT(MATCH($A$32,Data!$A$4:$A$18,0),
MATCH($B$3,Data!$B$4:$M$4,0))=2,
VLOOKUP($A$32,Data!$A$4:$M$18,MATCH($B$3,Data!$B$4:$M$4,0)+1,0),"")

Reply to this Message