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,MA TCH(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





.