Find Highest Match from Another Column

[leejohnson]

Hello,

I would like to use Excel formulas (not VBA code) to select the highest number from the corresponding column that matches the value given. I have attached a file as an example:

Say the value/name I am interested is 'Lee'. As shown on the attached, the highest number associated with Lee is 3. Hence, I would need the value 3 to be returned.
Say the value/name I am interested is 'Paul'. As shown on the attached, the highest number associated with Paul is 4. Hence, I would need the value 4 to be returned.

I have been pulling my hair out with this one, so if anyone can help it will be very much appreciated!

Many thanks!

Lee

[Ron Coderre]

Using your posted workbook
AND
D1: a name to match....LEE

This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
returns the max value associated with that name:

E1: =MAX(IF(D5:D11=D1,E5:E11))

However, since nobody I work with EVER remembers to C+S+E when editing an array formula,
this regular formula also returns the correct value:

E1: =MAX(INDEX((D5:D11=D1)*(E5:E11),0))

Is that something you can work with?

[leejohnson]

Thanks so much, Ron. It works a treat. Your help is much appreciated.

Kind regards,

Lee

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)

[leejohnson]

Thanks to Tony and Alkey too!

[Tony Valko]

You're welcome. We appreciate the feedback!

[AlKey]

With name in G5

=MAX(IF(D5:D11=G5,E5:E11))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Row\Col	D	E	F	G	H
5	Paul	1		Lee	3
6	Lee	3
7	Robin	2
8	Lee	2
9	Susan	5
10	Paul	4
11	Mike	2

[Tony Valko]

Here's another one if you're using Excel 2010 or later.

D1 = some name

=AGGREGATE(14,6,E5:E11/(D5:D11=D1),1))