Lookup Function

[Scott]

Hello,

I need some help with a lookup function but I'm not sure of the best way to
set up my formula. The question I'm trying to answer based on my data is "How
many days have passed until the next sale of this product?" This is the
formula I'm trying to input in Column D.

Here's a simplified example:
(A) (B) (C) (D)
DAY OF YEAR PRODUCT SOLD AMOUNT (DAYS TO NEXT SALE OF PROD)
1 A 100 19 (DAY 20-DAY1)
6 B 50 34 (DAY 40-DAY6)
13 C 275
20 A 225
22 A 500
28 C 200
40 B 225


Thanks in advance for your help.
-Scott

[Biff]

Hi!

Assume A1:D1 are column headers.

In D2 try this array formula entered with the key combo of
CTRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF(B$2:B$8=B2,A$2:A$8),COUNTIF(B$2:B2,B2)
+1)-SMALL(IF(B$2:B$8=B2,A$2:A$8),COUNTIF
(B$2:B2,B2))),"Last date for product " &B2,SMALL(IF
(B$2:B$8=B2,A$2:A$8),COUNTIF(B$2:B2,B2)+1)-SMALL(IF
(B$2:B$8=B2,A$2:A$8),COUNTIF(B$2:B2,B2)))

When you reach the last date for a product you'll receive
a return message of "Last date for product X".

Personally, I think you'd be better off listing all the
different products across a row and calculate them
individually.

Biff

>-----Original Message-----
>Hello,
>
>I need some help with a lookup function but I'm not sure
of the best way to
>set up my formula. The question I'm trying to answer
based on my data is "How
>many days have passed until the next sale of this
product?" This is the
>formula I'm trying to input in Column D.
>
>Here's a simplified example:
> (A) (B) (C) (D)
>DAY OF YEAR PRODUCT SOLD AMOUNT (DAYS TO NEXT SALE OF
PROD)
> 1 A 100 19 (DAY 20-DAY1)
> 6 B 50 34 (DAY 40-DAY6)
> 13 C 275
> 20 A 225
> 22 A 500
> 28 C 200
> 40 B 225
>
>
>Thanks in advance for your help.
>-Scott
>
>
>
>
>.
>

[Aladin Akyurek]

Let A1:C8 house the sample you provided, including the labels.

In D2 enter & copy down as far as needed:

=IF(COUNTIF(B3:$B$8,B2),INDEX(A3:$A$8,MATCH(B2,B3:$B$8,0))-A2,"")

Scott wrote:
> Hello,
>
> I need some help with a lookup function but I'm not sure of the best way to
> set up my formula. The question I'm trying to answer based on my data is "How
> many days have passed until the next sale of this product?" This is the
> formula I'm trying to input in Column D.
>
> Here's a simplified example:
> (A) (B) (C) (D)
> DAY OF YEAR PRODUCT SOLD AMOUNT (DAYS TO NEXT SALE OF PROD)
> 1 A 100 19 (DAY 20-DAY1)
> 6 B 50 34 (DAY 40-DAY6)
> 13 C 275
> 20 A 225
> 22 A 500
> 28 C 200
> 40 B 225
>
>
> Thanks in advance for your help.
> -Scott
>
>
>
>

[Biff]

Hi!

Much shorter but not consistent.

The last entries for products A and C return "" but the
last entry for product B returns zero.

I think it's best to replace the "" with some sort of
message.

Biff

>-----Original Message-----
>Let A1:C8 house the sample you provided, including the
labels.
>
>In D2 enter & copy down as far as needed:
>
>=IF(COUNTIF(B3:$B$8,B2),INDEX(A3:$A$8,MATCH
(B2,B3:$B$8,0))-A2,"")
>
>Scott wrote:
>> Hello,
>>
>> I need some help with a lookup function but I'm not
sure of the best way to
>> set up my formula. The question I'm trying to answer
based on my data is "How
>> many days have passed until the next sale of this
product?" This is the
>> formula I'm trying to input in Column D.
>>
>> Here's a simplified example:
>> (A) (B) (C) (D)
>> DAY OF YEAR PRODUCT SOLD AMOUNT (DAYS TO NEXT SALE OF
PROD)
>> 1 A 100 19 (DAY 20-DAY1)
>> 6 B 50 34 (DAY 40-DAY6)
>> 13 C 275
>> 20 A 225
>> 22 A 500
>> 28 C 200
>> 40 B 225
>>
>>
>> Thanks in advance for your help.
>> -Scott
>>
>>
>>
>>
>.
>

[Aladin Akyurek]

Either:

don't copy the formula to the cell correponding to the last cell

or:

wrap it inside of an IF...

=IF(B3<>"",IF(COUNTIF(B3:$B$8,B2),INDEX(A3:$A$8,MATCH(B2,B3:$B$8,0))-A2,""),"")

Biff wrote:
> Hi!
>
> Much shorter but not consistent.
>
> The last entries for products A and C return "" but the
> last entry for product B returns zero.
>
> I think it's best to replace the "" with some sort of
> message.
>
> Biff
>
>
>>-----Original Message-----
>>Let A1:C8 house the sample you provided, including the
>
> labels.
>
>>In D2 enter & copy down as far as needed:
>>
>>=IF(COUNTIF(B3:$B$8,B2),INDEX(A3:$A$8,MATCH
>
> (B2,B3:$B$8,0))-A2,"")
>
>>Scott wrote:
>>
>>>Hello,
>>>
>>>I need some help with a lookup function but I'm not
>
> sure of the best way to
>
>>>set up my formula. The question I'm trying to answer
>
> based on my data is "How
>
>>>many days have passed until the next sale of this
>
> product?" This is the
>
>>>formula I'm trying to input in Column D.
>>>
>>>Here's a simplified example:
>>> (A) (B) (C) (D)
>>>DAY OF YEAR PRODUCT SOLD AMOUNT (DAYS TO NEXT SALE OF
>
> PROD)
>
>>> 1 A 100 19 (DAY 20-DAY1)
>>> 6 B 50 34 (DAY 40-DAY6)
>>> 13 C 275
>>> 20 A 225
>>> 22 A 500
>>> 28 C 200
>>> 40 B 225
>>>
>>>
>>>Thanks in advance for your help.
>>>-Scott
>>>
>>>
>>>
>>>
>>
>>.
>>