Eliminate the #VALUE! response.

I

inthestands

I have a vlookup formula where I am looking at three different warehouses to
locate the amount of material that I have on order. 1 or 2 of the
spreadsheets will sometimes not have the same sku in the warehouse. I assume
this is why I am getting the #VAULE! Response. I have copied the formula
that I am using. How can I correct it so that I can get the quantities when
there are some, but not have the #VALUE response show at all. I would prefer
that a blank shows instead.

=IF(ISNA(VLOOKUP(B2,'data sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data
sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data
sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data
sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data
mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0))

Thanks in advance
 
J

Javier Diaz

use IsErr like below, IsErr ignores the ISNA so that you can use both ISNA
and ISERR, just in case you want to keep the ISNA.

=if(iserr(IF(ISNA(VLOOKUP(B2,'data
sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data
sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data
sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data
sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data
mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data
mod'!$A$2:$M$5803,13,0)),"",IF(ISNA(VLOOKUP(B2,'data
sac'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data
sac'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data
sj'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data
sj'!$A$2:$M$5803,13,0))+IF(ISNA(VLOOKUP(B2,'data
mod'!$A$2:$M$5903,13,0)),"",VLOOKUP(B2,'data mod'!$A$2:$M$5803,13,0)))
 
T

T. Valko

The problem is that you're adding the results of all the lookups together.
If any return "" that causes the #VALUE! error. You could return 0 instead
of "" and that'll correct the #VALUE! error. Then, if you don't want to see
the result as 0 you could use conditional formatting to hide the 0.
 
J

Javier Diaz

Good Point T Valko.

T. Valko said:
The problem is that you're adding the results of all the lookups together.
If any return "" that causes the #VALUE! error. You could return 0 instead
of "" and that'll correct the #VALUE! error. Then, if you don't want to see
the result as 0 you could use conditional formatting to hide the 0.
Click to expand...
 
I

inthestands

Thank you for the 0 vs the "". It did the trick. And thanks to the other
responders for their advice. It is very appreciated.
 
Top