Index/Match Help

[Ben]

I have the following formula to extract the date of the
most recent sale from a list.

=INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATCH
(O4,O13:T13,0))

I would like to build another formula to extract the next
recent sales date entry. This entry may have the same
date as the most recent in the list. I have tried
entering -1 in various places, only to retrive errors or
yesterdays date with no data (in the case there was no
sale yesterday).

Does anyone have any ideas?

Thank you.

 

[Jerry W. Lewis]

You have not said what is in the various ranges referenced by your
formula. Assuming that you have a range of sale dates, =MAX(dateRange)
is the date of the most recent sale and =LARGE(dateRange,2) is the date
of the second most recent sale.

Jerry

 

[Ben]

I'm not sure I understand... maybe this helps:

SALES - total area of data, including date, customer, etc.
SALE_DATE - list of dates, may have skipped/blank rows
O4 - Cell w/ "DATE", "Customer" etc
O13:T13 - List titles including "DATE", "CUSTOMER" etc

SALE_DATE never exceeds today() but may equal it.
Numerous entries may have the same date. Entry to entry
may skip numerous days, ie O14=11/11/04 and O15=12/1/04.

Is this better?

Thanks

 

[Jerry W. Lewis]

You said that you want to extract the dates of the two most recent
sales. Why do the following not do what you want?
=MAX(SALE_DATE)
=LARGE(SALE_DATE,2)

Jerry

 

[Guest]

Actually I want the last 5 dates with additional data with
each date.

 

[Ben]

Thanks Jerry. I am getting ahead of myself. I pick the
date with the method you give, then the additional
information using the extacted date in the first match
clause. I was making it too complicated.

Thank you very much.
Ben

 

[Jerry W. Lewis]

You're welcome; glad it helped.

Jerry