Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup latest entered match
I am trying to lookup the last entry of a name in an array. For example:
A B C 1 Fred 5 1/5/05 2 Barbey 4 1/6/06 3 Betty 10 2/12/06 4 Fred 7 5/15/06 5 Wilma 7 6/1/06 6 Dino 2 6/12/06 =vlookup("Fred",a1:c4,3,1) will only give me the first "Fred" date (1/5/05) in column C. I am looking for a way to return the last entered "Fred". |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup latest entered match
One way (array entered, must confirm w/Control+Shift+Enter)
=INDEX(C1:C6,LARGE((A1:A6="fred")*ROW(INDIRECT("1: "&ROWS(A1:A6))),1)) If you have more than two matches and want to return one in between, changing the 1 at the end (second argument of the LARGE function) to 2 will return the second to last, etc. You can also change LARGE to SMALL (it works the same way, but will start counting from the beginning of your data). "Smugga" wrote: I am trying to lookup the last entry of a name in an array. For example: A B C 1 Fred 5 1/5/05 2 Barbey 4 1/6/06 3 Betty 10 2/12/06 4 Fred 7 5/15/06 5 Wilma 7 6/1/06 6 Dino 2 6/12/06 =vlookup("Fred",a1:c4,3,1) will only give me the first "Fred" date (1/5/05) in column C. I am looking for a way to return the last entered "Fred". |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup latest entered match
Assuming that A2:C7 contains the data, try...
=LOOKUP(2,1/(A2:A7="Fred"),C2:C7) Hope this helps! In article , Smugga wrote: I am trying to lookup the last entry of a name in an array. For example: A B C 1 Fred 5 1/5/05 2 Barbey 4 1/6/06 3 Betty 10 2/12/06 4 Fred 7 5/15/06 5 Wilma 7 6/1/06 6 Dino 2 6/12/06 =vlookup("Fred",a1:c4,3,1) will only give me the first "Fred" date (1/5/05) in column C. I am looking for a way to return the last entered "Fred". |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup latest entered match
It almost answers the question. I would like the date returned, and not
Fred... What I am searching for is the last time (date) that Fred was entered. -G "Domenic" wrote: Assuming that A2:C7 contains the data, try... =LOOKUP(2,1/(A2:A7="Fred"),C2:C7) Hope this helps! In article , Smugga wrote: I am trying to lookup the last entry of a name in an array. For example: A B C 1 Fred 5 1/5/05 2 Barbey 4 1/6/06 3 Betty 10 2/12/06 4 Fred 7 5/15/06 5 Wilma 7 6/1/06 6 Dino 2 6/12/06 =vlookup("Fred",a1:c4,3,1) will only give me the first "Fred" date (1/5/05) in column C. I am looking for a way to return the last entered "Fred". |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup latest entered match
Have you tried the formula? The formula will find the last instance of
'Fred', which in your example would be Row 4, and return the corresponding value in Column C, which in your example would be 5/15/06. In article , Smugga wrote: It almost answers the question. I would like the date returned, and not Fred... What I am searching for is the last time (date) that Fred was entered. -G "Domenic" wrote: Assuming that A2:C7 contains the data, try... =LOOKUP(2,1/(A2:A7="Fred"),C2:C7) Hope this helps! In article , Smugga wrote: I am trying to lookup the last entry of a name in an array. For example: A B C 1 Fred 5 1/5/05 2 Barbey 4 1/6/06 3 Betty 10 2/12/06 4 Fred 7 5/15/06 5 Wilma 7 6/1/06 6 Dino 2 6/12/06 =vlookup("Fred",a1:c4,3,1) will only give me the first "Fred" date (1/5/05) in column C. I am looking for a way to return the last entered "Fred". |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup latest entered match
Thanks Domenic! It works.
-Smugga "Domenic" wrote: Assuming that A2:C7 contains the data, try... =LOOKUP(2,1/(A2:A7="Fred"),C2:C7) Hope this helps! In article , Smugga wrote: I am trying to lookup the last entry of a name in an array. For example: A B C 1 Fred 5 1/5/05 2 Barbey 4 1/6/06 3 Betty 10 2/12/06 4 Fred 7 5/15/06 5 Wilma 7 6/1/06 6 Dino 2 6/12/06 =vlookup("Fred",a1:c4,3,1) will only give me the first "Fred" date (1/5/05) in column C. I am looking for a way to return the last entered "Fred". |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup latest entered match
Hi Domenic!
I've the same problem and your solution works very well but... I can't understand it... :-( What is: 1/(A2:A7="Fred") ? What does it mean? Thank you very much... paola "Domenic" wrote: Assuming that A2:C7 contains the data, try... =LOOKUP(2,1/(A2:A7="Fred"),C2:C7) Hope this helps! In article , Smugga wrote: I am trying to lookup the last entry of a name in an array. For example: A B C 1 Fred 5 1/5/05 2 Barbey 4 1/6/06 3 Betty 10 2/12/06 4 Fred 7 5/15/06 5 Wilma 7 6/1/06 6 Dino 2 6/12/06 =vlookup("Fred",a1:c4,3,1) will only give me the first "Fred" date (1/5/05) in column C. I am looking for a way to return the last entered "Fred". |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup latest entered match
Hi Domenic!
I have the same problem and your solution works very well but... I can't understand it... :-( What is 1/(A2:A7="Fred")? What does it means? Thank you very much... paola "Domenic" wrote: Assuming that A2:C7 contains the data, try... =LOOKUP(2,1/(A2:A7="Fred"),C2:C7) Hope this helps! In article , Smugga wrote: I am trying to lookup the last entry of a name in an array. For example: A B C 1 Fred 5 1/5/05 2 Barbey 4 1/6/06 3 Betty 10 2/12/06 4 Fred 7 5/15/06 5 Wilma 7 6/1/06 6 Dino 2 6/12/06 =vlookup("Fred",a1:c4,3,1) will only give me the first "Fred" date (1/5/05) in column C. I am looking for a way to return the last entered "Fred". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compound Lookup, Large/Small, Match... | Excel Worksheet Functions | |||
Lookup Function and Match | Excel Discussion (Misc queries) | |||
Complicated If Then / V Lookup / Match Statement... | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Lookup / Match help ??? | Excel Discussion (Misc queries) |