Thanks to everyone for their help. This worked great.

Conor

Bob Phillips wrote:
This will get you the latest value

=INDEX($E$1:$E$7000,MAX(IF(B2=$B$1:$B$7000,ROW($B$ 1:$B$7000))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I don't know if I have the right functions but I have an issue that I
am running into with a lot of data I am dealing with. I need to find
the best way to figure this out since it is something I deal with on a
monthly basis. See the sample data below and let me know what excel
functions might help me out if you can. Thanks in advance for your
help.

Run Date Item Sell Start
Sell End Copies Sold

10/1 CD123 10/1/2006
10/31/2006 1000
10/1 CD456 10/1/2006
10/31/2006 3500

10/2 CD123 10/1/2006
10/21/2006 1500
10/2 CD456 10/1/2006
10/31/2006 4500


Basically the scenario/example above shows a line item, in this case
CD123 and CD456...CD123 was scheduled to be sold from the 1st to the
31st and by the date (let's say the 15th), 1000 copies were sold...Then
on the 21st, CD123 was pulled from the shelves and only sold 1500
copies total. I have roughly 6000 lines of data like this that I keep
track of on a daily basis. At the end of the month, I need an easy way
to go back and pull all the line items and have it show me the number
of copies sold. The problem I have is that for 15 days, I have line
items that have a start date of 10/1 and the end date of 10/31, then on
the 15th, I get a new line item that has a new end data of 10/21...Even
though Column A is the same, I can't get the formula to return the max
number if the end date changes. I normally get the 1000 to show up but
not the 1500.

It wouldn't be so bad if I didn't have roughly 7000 rows of data to
sift through. I don't always know when end dates have changed. Please
help if you can. Thanks again.

Conor