How to look for best Scorer if I have two criteria

[ChrisCA89]

Hello everyone,

I'm stuck and I cant solve the following problem.
I have to determine the user with the highest achieved score for Criteria A. However, I might have a couple of users who scored the same result for Criteria A. In order to determine the highest scorer I am then looking Criteria B. Out of those who scored the highest result under Criteria A, who actually achieved the best result under Criteria B.

Screenshot.PNG

As highlighted in the attached file, the formula that I am looking for should give me the result "User 7".
Both User 7, User 10 and User 12 achieved the same highest score under Criteria A. Out of the shortlisted users, User 7 scored the highest under Criteria B though. I have to determine this user.

I think the formula that I am looking for is a combination of Index(), Match() and Sumproduct() and Max()... but I might be wrong.
Your help is highly appreciated, thank you.
best regards
Chris

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[AliGW]

Try this:

=INDEX($B$3:$B$15,MATCH(LARGE(IF($C$3:$C$15=LARGE($C$3:$C$15,1),$D$3:$D$15),1),$D$3:$D$15,0))

... 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.

Will only work properly, of course, if you do not have a tie for first place.

[davsth]

I did think of something similar to Ali but if say the value in D2 is also 3.05, it will return user1 rather than user 7

Adding a column of c*1000+d and finding the max of this works, if the data is Representative of reality, but not a one cell solution.

Still thinking

[ChrisCA89]

Hi =)

Thank you so much, it works just perfect.
Thank you for your note with the tie. In my scenario it doesnt matter as I am using the User to display the benchmark. Wouldnt matter if two users achieved exactly the same score.
Thank you again. =)!

[AliGW]

Fine - it will only display the first to meet the criteria.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[davsth]

but it wont. In the example I gave User 1 doesn't have the highest score for criteria A, but has the same Criteria B score, as the User who has the highest Criteria A score and is using criteria B as the split

That was my point if you user the formula and overtype cell D3, the formula will return user 1, even tho C3 is not the maximum of criteria A

[AliGW]

Ugh! Good point ...

[AliGW]

This should do it:

=LOOKUP(2,1/(($C$3:$C$15=MAX($C$3:$C$15))*($D$3:$D$15=MAX(IF($C$3:$C$15=MAX($C$3:$C$15),$D$3:$D$15)))),$B$3:$B$15)

... 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.