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

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

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

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