Return value in adjacent cell

[Guest]

I used Biff's response to whub3 (4/15/06) to solve a similar problem but I
get a #value! error if the product isn't listed in the range.

19 products are listed S203:S223
the % recovery for each product is listed T203:T223
the Gross MSF is manually entered in V203:V223
the formula to multiply the % recovery by the gross MSF is listed U203:U223

The products that are made during the production day are listed AB165:AB171
with the corresponding gross MSF in AC165:AC171 (each day, this information
changes - not all products are made).
What I would like is for the gross MSF to be pulled from AC165:AC171 and
entered in V203:V223. This is the formula I got from Biff's example:

=IF(S203="","",VLOOKUP(S203,AB165:AC171,2,0))

(S203 will never be blank but it said the formula was bad when I got rid of
that part.)

If S203 product was made then it pulls the correct MSF but if not, a #VALUE!
error is returned and the % recovery for the day (V227) is a #VALUE! error.
I tried the SUMIF formula to get rid of the error but it didn't work.

Thanks

 

[Biff]

Don't blame this on me! <g>

If the lookup value is not found the formula will return #N/A, not #VALUE!.

(S203 will never be blank but it said the formula was bad when I got rid
of
that part.)

Try it like this:

=IF(ISNA(MATCH(S203,AB165:AB171,0)),0,VLOOKUP(S203,AB165:AC171,2,0))

Biff

 

[Guest]

Hi Biff,

Thanks for the reply, but now all the gross MSF cells (col. V) remain empty
and I entered sample numbers for four products.

Tammy

 

[Biff]

I'm not following you on this.

If the product wan't listed then the formula you were using would return an
error (as it should).

The formula I suggested will eliminate the error. What exactly do you want
if the product isn't listed?

Biff

 

[Guest]

If 3 of 19 products were made, I want the gross MSF for the 3 products to
show up in v column so the daily % recovery can be figured using those 3
products and the remaing 16 products to remain blank.

Tammy

 

[Guest]

Using the information from this forum and from Chip Pearson's website, I was
able to put together a formula that works:

=IF(ISERROR(VLOOKUP(S203,$AB$165:$AC$171,2,0)),"",VLOOKUP(S203,$AB$165:$AC$171,2,0))

Thanks.

Tammy