Lookup unsorted list

W

why-J

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.
 
M

Mike H

Try this

=LOOKUP(2,1/(A1:A20=C1),B1:B20)

Where C1 is the item you are looking up.

Mike
 
L

Lars-Åke Aspelin

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
 
L

Lars-Åke Aspelin

This formula relies on that column B is sorted with increasing dates
Of that we know nothing I think.

Lars-Åke
 
M

Mike H

Hi,

It doesn't rely on that and will work with either/both columns
sorted/unsorted. Have you tried it?

Mike
 
M

Mike H

This returns the second instance, not the last

Lars-Ã…ke Aspelin said:
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
 
L

Lars-Åke Aspelin

Yes I did try it.
My interpretation of "the most recent date" is the date with the
highest value, not the date placed on the highest row number.

Lars-Åke
 
L

Lars-Åke Aspelin

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top