ExcelBanter - Index/Match Help

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Index/Match Help (https://www.excelbanter.com/excel-worksheet-functions/7287-index-match-help.html)

Index/Match Help

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.

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 wrote:

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.

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

-----Original Message-----
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 wrote:

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.

.

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

Ben wrote:

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

-----Original Message-----
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 wrote:

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.

Actually I want the last 5 dates with additional data with
each date.
-----Original Message-----
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

Ben wrote:

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

-----Original Message-----
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 wrote:

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

=INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATC H
(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.

.

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
-----Original Message-----
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

Ben wrote:

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

-----Original Message-----
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 wrote:

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

=INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATC H
(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.

.

You're welcome; glad it helped.

Jerry

Ben wrote:

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