LARGE formula when logical IF is true

hobeee

New Member
Joined
Dec 21, 2010
Messages
5
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top