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

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

"TammyS" wrote in message
...
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

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" wrote:

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

"TammyS" wrote in message
...
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

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

"TammyS" wrote in message
...
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" wrote:

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

"TammyS" wrote in message
...
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

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

"Biff" wrote:

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

"TammyS" wrote in message
...
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" wrote:

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

"TammyS" wrote in message
...
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

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


"Biff" wrote:

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

"TammyS" wrote in message
...
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" wrote:

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

"TammyS" wrote in message
...
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