Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup unsorted list
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup unsorted list
Try this
=LOOKUP(2,1/(A1:A20=C1),B1:B20) Where C1 is the item you are looking up. Mike "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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup unsorted list
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup unsorted list
This formula relies on that column B is sorted with increasing dates
Of that we know nothing I think. Lars-Åke On Thu, 24 Jul 2008 00:38:00 -0700, Mike H wrote: Try this =LOOKUP(2,1/(A1:A20=C1),B1:B20) Where C1 is the item you are looking up. Mike "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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup unsorted list
Hi,
It doesn't rely on that and will work with either/both columns sorted/unsorted. Have you tried it? Mike "Lars-Ã…ke Aspelin" wrote: This formula relies on that column B is sorted with increasing dates Of that we know nothing I think. Lars-Ã…ke On Thu, 24 Jul 2008 00:38:00 -0700, Mike H wrote: Try this =LOOKUP(2,1/(A1:A20=C1),B1:B20) Where C1 is the item you are looking up. Mike "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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup unsorted list
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup unsorted list
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 On Thu, 24 Jul 2008 01:12:03 -0700, Mike H wrote: Hi, It doesn't rely on that and will work with either/both columns sorted/unsorted. Have you tried it? Mike "Lars-Åke Aspelin" wrote: This formula relies on that column B is sorted with increasing dates Of that we know nothing I think. Lars-Åke On Thu, 24 Jul 2008 00:38:00 -0700, Mike H wrote: Try this =LOOKUP(2,1/(A1:A20=C1),B1:B20) Where C1 is the item you are looking up. Mike "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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup unsorted list
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning values from an unsorted list. | Excel Discussion (Misc queries) | |||
How do I find top 3 in an unsorted list? | Excel Worksheet Functions | |||
lookup unsorted list | Excel Discussion (Misc queries) | |||
An unsorted list in vlookup | Excel Worksheet Functions | |||
Matching unsorted lookup values | Excel Worksheet Functions |