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

[Katlyn Jones]

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

 

[JP]

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

 

[Harlan Grove]

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),"")