Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and match on 2 largest value
A B
1 Bill 1/1/2003 2 Ralph 11/14/2004 3 Mike 11/16/2004 4 Ralph 2/7/2005 5 Ralph 1/14/2006 7 Ralph I am having more trouble than I thought I would with this one. Tried to do index and match formula with small function in cell B7 that would return 2 largest value for Ralph... in this case that value would be 2/7/2005. Column A cannot be alphabetical. Column B is chronological. Thanks for any help, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and match on 2 largest value
Try...
=LARGE(IF($A$1:$A$5=A7,$B$1:$B$5),2) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article . com, "Steve" wrote: A B 1 Bill 1/1/2003 2 Ralph 11/14/2004 3 Mike 11/16/2004 4 Ralph 2/7/2005 5 Ralph 1/14/2006 7 Ralph I am having more trouble than I thought I would with this one. Tried to do index and match formula with small function in cell B7 that would return 2 largest value for Ralph... in this case that value would be 2/7/2005. Column A cannot be alphabetical. Column B is chronological. Thanks for any help, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and match on 2 largest value
=SUMPRODUCT( LARGE( ( A1:A5 = "Ralph" ) * B1:B5, 2 ) )
Or the array formula {=LARGE( ( A1:A5 = "Ralph" ) * B1:B5, 2 )} -- Regards, Luc. "Festina Lente" "Steve" wrote: A B 1 Bill 1/1/2003 2 Ralph 11/14/2004 3 Mike 11/16/2004 4 Ralph 2/7/2005 5 Ralph 1/14/2006 7 Ralph I am having more trouble than I thought I would with this one. Tried to do index and match formula with small function in cell B7 that would return 2 largest value for Ralph... in this case that value would be 2/7/2005. Column A cannot be alphabetical. Column B is chronological. Thanks for any help, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and match on 2 largest value
Thanks Domenic. I appreciate the help !
Domenic wrote: Try... =LARGE(IF($A$1:$A$5=A7,$B$1:$B$5),2) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article . com, "Steve" wrote: A B 1 Bill 1/1/2003 2 Ralph 11/14/2004 3 Mike 11/16/2004 4 Ralph 2/7/2005 5 Ralph 1/14/2006 7 Ralph I am having more trouble than I thought I would with this one. Tried to do index and match formula with small function in cell B7 that would return 2 largest value for Ralph... in this case that value would be 2/7/2005. Column A cannot be alphabetical. Column B is chronological. Thanks for any help, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index, Match and filters | Excel Discussion (Misc queries) | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |