LARGE formula when logical IF is true

[hobeee]

Hello all,
I have a list in column A and B like below. The list below contains 100 rows.
I also have "Dave" in column C and column E "John"



I would like to use the IF function combined with LARGE array function in D1, to tell me the highest value that "Dave". Followed by D2, the 2nd highest.... I don't know of a formula to perform LARGE on one condition. Pls help.



I am familiar with the IF functions and LARGE function, but not using them together. Or if there is another approach, I would like to hear it.



A B C D E F

4 John Dave John

5 Dave Dave John
10 George Dave John

12 Dave
2 John
7 George
0 Dave
0 George

........


Thx, John

 

[hobeee]

Resubmit for clarification of the list :

A B
4 John
5 Dave
10 George
12 Dave
2 John
7 George
0 Dave
0 George
........

C D
Dave
Dave
Dave

E F
John
John
John

 

[Marcelo Branco]

Hi and welcome to Mr Excel forum,

Try this array formula in D1
=IF(COUNTIF($B$1:$B$100,$C$1)>=ROWS($D$1:D1),LARGE(IF($B$1:$B$100=$C$1,$A$1:$A$100),ROWS($D$1:D1)),"")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

Use a similar formula in F1 (adjust the ranges accordingly)

M.

 

[hobeee]

Thanks. That did the trick.