Return value in adjacent cell
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
|