Return a name from two columns of data

[geddes_3]

I'm trying to figure out a formula I can use that will return a name based on two sets of data. I need the name that has the highest number AND the greater percentage. I attached a screenshot of what I need. In this case, in cell D3 I need it to say C (since it has the highest amount in column B, AND the higher percentage between the two that have the highest amount). I just can't figure out how to return the higher percentage, since it's not necessarily the highest from the set.

[NBVC]

Try:

=INDEX(A1:A5,MATCH(1,(MAX(B1:B5)=B1:B5)*(C1:C5=MAX(IF(B1:B5=MAX(B1:B5),C1:C5))),0))

confirmed with CTRL+SHIFT+ENTER not just ENTER

[Andrew-R]

Here you go.

[geddes_3]

I tried that formula but didn't get the desired result. Ideally I'd like to copy the formula through all cells in column D, but when I did that it returned a different name in cells D4 and D5. Is there a way to have it only return one name in the appropriate cell (with the same formula in each cell in column D?). Thanks for your help

[Andrew-R]

Like this?

[NBVC]

So are you trying to do a top 5 or something like that?

[geddes_3]

NBVC - Yeah it is like a top 5, but I have like 100 different names and only need 1 name returned.

Andrew-R - I realized that I actually don't need to copy the formula through all the cells, sorry about that.

I'm going to try those formulas again and see if it works. Thanks again

[NBVC]

I suggest we use a helper column to rank the numbers first..

so in D1:

=RANK($B1,$B$1:$B$5)+SUMPRODUCT(--($B$1:$B$5=$B1),--($C1<$C$1:$C$5))

copied dow.

Then in E1:

=INDEX($A$1:$A$5,MATCH(ROWS($A$1:$A1),$D$1:$D$5,0))

copied down to extract the corresponding letters from column A

adjust ranges to suit.

[geddes_3]

Brilliant! That did it, thanks so much

[NBVC]

You are welcome.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save