On Thu, 24 Jul 2008 00:22:01 -0700, why-J
wrote:

I have a very simple two column list. Col-A is "Products" col-B is "Date
Purchase". Col-A is un-sorted and various products are repeated several
times. I want a lookup formula that would tell me the most recent date when a
particular product was purchased.

If col-A was sorted ascending, I could use
=LOOKUP(1E+100,INDEX(A2:B8,MATCH(A12,A2:A8,1),0)) . Can someone please help.

Try this formula:
(Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER)

=MAX((A2:A8=A12)*(B2:B8))

Hope this helps / Lars-Åke

This returns the second instance, not the last

"Lars-Ã…ke Aspelin" wrote:

On Thu, 24 Jul 2008 00:22:01 -0700, why-J
wrote:

I have a very simple two column list. Col-A is "Products" col-B is "Date
Purchase". Col-A is un-sorted and various products are repeated several
times. I want a lookup formula that would tell me the most recent date when a
particular product was purchased.

If col-A was sorted ascending, I could use
=LOOKUP(1E+100,INDEX(A2:B8,MATCH(A12,A2:A8,1),0)) . Can someone please help.

Try this formula:
(Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER)

=MAX((A2:A8=A12)*(B2:B8))

Hope this helps / Lars-Ã…ke

My interpretation of "the most recent date" is the date with the
highest value, not the date placed on the highest row number.
The most recent date may be placed on any row I guess.

Lars-Åke


On Thu, 24 Jul 2008 01:13:08 -0700, Mike H
wrote:

This returns the second instance, not the last

"Lars-Åke Aspelin" wrote:

On Thu, 24 Jul 2008 00:22:01 -0700, why-J
wrote:

I have a very simple two column list. Col-A is "Products" col-B is "Date
Purchase". Col-A is un-sorted and various products are repeated several
times. I want a lookup formula that would tell me the most recent date when a
particular product was purchased.

If col-A was sorted ascending, I could use
=LOOKUP(1E+100,INDEX(A2:B8,MATCH(A12,A2:A8,1),0)) . Can someone please help.

Try this formula:
(Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER)

=MAX((A2:A8=A12)*(B2:B8))

Hope this helps / Lars-Åke