Home |
Search |
Today's Posts |
#1
|
|||
|
|||
find LAST match in column
Who knows how I can lookup the LAST match in a column? i.e., in A1:A1000 there's 1000 names, unsorted, with multiple names repeated. If I want to find the last mention of 'Bob' and then get the value of the cell next to it, how can I do that? The first match is no problem, but I can't figure out the last. Thanks muchly, Marinus. -- Pantryman ------------------------------------------------------------------------ Pantryman's Profile: http://www.excelforum.com/member.php...o&userid=15233 View this thread: http://www.excelforum.com/showthread...hreadid=275479 |
#2
|
|||
|
|||
=INDEX(B:B,MAX(IF(A1:A1000="Jack",ROW(A1:A1000)))) ...entered using CONTROL+SHIFT+ENTER. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=275479 |
#3
|
|||
|
|||
"Domenic" wrote...
=INDEX(B:B,MAX(IF(A1:A1000="Jack",ROW(A1:A1000))) ) ..entered using CONTROL+SHIFT+ENTER. Be more efficient to use =LOOKUP(1,1/(A1:A100="Bob"),B1:B100) |
#4
|
|||
|
|||
Harlan Grove Wrote: "Domenic" wrote... =INDEX(B:B,MAX(IF(A1:A1000="Jack",ROW(A1:A1000))) ) ..entered using CONTROL+SHIFT+ENTER. Be more efficient to use =LOOKUP(1,1/(A1:A100="Bob"),B1:B100) Yes, definitely! Thanks for the reminder, Harlan! Although, wouldn't 2 serve better as the lookup value? For example, take the following table... Bob a Jack b Jane c Bob d Jill e Phil f Bob g Jason h If you use... =LOOKUP(1,1/(A1:A100="Bob"),B1:B100) ...the correct result is returned, that being "g". However, if you use... =LOOKUP(1,1/(A1:A8="Bob"),B1:B8) ...an incorrect result is returned, that being "d". Using 2 as the lookup value returns the correct result regardless of the range one uses. -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=275479 |
#5
|
|||
|
|||
Assuming that A1:A1000 is a formula-free range... =LOOKUP(REPT("z",255),A1:A1000,B1:B1000) Pantryman Wrote: Who knows how I can lookup the LAST match in a column? i.e., in A1:A1000 there's 1000 names, unsorted, with multiple names repeated. If I want to find the last mention of 'Bob' and then get the value of the cell next to it, how can I do that? The first match is no problem, but I can't figure out the last. Thanks muchly, Marinus. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=275479 |
#6
|
|||
|
|||
Disregard this for it's not a conditional lookup as you require.
"Aladin Akyurek" wrote in message ... Assuming that A1:A1000 is a formula-free range... =LOOKUP(REPT("z",255),A1:A1000,B1:B1000) Pantryman Wrote: Who knows how I can lookup the LAST match in a column? i.e., in A1:A1000 there's 1000 names, unsorted, with multiple names repeated. If I want to find the last mention of 'Bob' and then get the value of the cell next to it, how can I do that? The first match is no problem, but I can't figure out the last. Thanks muchly, Marinus. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=275479 |
#7
|
|||
|
|||
Hi Domenic,
I just saw this correspondence and I wonder if you could explain the formula because i do not understand how it can return the right value (even though it does!) For instance, how do the formula know that the input 1/A1:A1000<"" refers to the last cell? Thanks Adam "Domenic" skrev: Yes, definitely! Thanks for the reminder, Harlan! Although, wouldn't 2 serve better as the lookup value? For example, take the following table... Bob a Jack b Jane c Bob d Jill e Phil f Bob g Jason h If you use... =LOOKUP(1,1/(A1:A100="Bob"),B1:B100) ...the correct result is returned, that being "g". However, if you use... =LOOKUP(1,1/(A1:A8="Bob"),B1:B8) ...an incorrect result is returned, that being "d". Using 2 as the lookup value returns the correct result regardless of the range one uses. |
#8
|
|||
|
|||
The formula extends a formula for finding the (position of) last numeric
value in a range. The extension is due to Harlan Grove. How it works is explained he http://tinyurl.com/7ysq5 Adam wrote: Hi Domenic, I just saw this correspondence and I wonder if you could explain the formula because i do not understand how it can return the right value (even though it does!) For instance, how do the formula know that the input 1/A1:A1000<"" refers to the last cell? Thanks Adam "Domenic" skrev: Yes, definitely! Thanks for the reminder, Harlan! Although, wouldn't 2 serve better as the lookup value? For example, take the following table... Bob a Jack b Jane c Bob d Jill e Phil f Bob g Jason h If you use... =LOOKUP(1,1/(A1:A100="Bob"),B1:B100) ...the correct result is returned, that being "g". However, if you use... =LOOKUP(1,1/(A1:A8="Bob"),B1:B8) ...an incorrect result is returned, that being "d". Using 2 as the lookup value returns the correct result regardless of the range one uses. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find rows for unique data in 1 column and different data in other. | Excel Discussion (Misc queries) | |||
How do I find the first value in a column less than a number? | Excel Worksheet Functions | |||
Find AVG/MIN of a Column, excluding 0's and NULL's? | Excel Worksheet Functions | |||
find and match the max | Excel Worksheet Functions | |||
Use MATCH to find position of max in 2D range? | Excel Worksheet Functions |