Getting name depending on max score

[Tsetsko]

Hi guys,

I am trying to get the name of a person who scored the maximum in a game. I have used vlookup before, but from what I can see index and match are a more powerful tools because they works vertically as well.
As you can see from the spreadsheet that I have attached, I have given it a go in I6.
If you solve my problem can you please explain in detail how you do it? On YouTube the videos show examples without actually explaining the exact meaning of the functions, so I think I am a bit lost.


Best Regards,

Tsvetan

[Phuocam]

Try :

=INDEX($A$1:$F$1,AGGREGATE(15,6,COLUMN($A$2:$F$10)/($A$2:$F$10=MAX($A$2:$F$10)),1))

[JohnTopley]

Use this which will list ALL names if there are multiple max scores

in J5

=IFERROR(INDEX($1:$1,SMALL(IF(A$2:F$10=I$5,COLUMN(A$2:F$10)),ROWS($1:1))),"")

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

Copy down

Above formula supplied by 63Falcondude in your last post

[gfranco]

Hello.
For the structure shown (with data starting at "A1") the following formula will suffice:

Please Login or Register  to view this content.

How does she work?

It will create an array of divisions of the columns of "A: F" / by searching for the present value in "i5" in the presented values and will return the lowest true value found (in this specific case, either the largest or the smallest. I chose the smaller one.
I'm not very good at explaining but that's what's happening.

[FlameRetired]

Tsetsko it appears that the focus of your question is how INDEX - MATCH works and how can it be applied to this problem.

Here is one way to apply INDEX - MATCH to this problem. It is the simplest way I could think of. This formula must be array entered.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

How does it work?

If you are not familiar with them there are two tools in Excel which are good for analyzing, trouble shooting and self instruction. They are the F9 function key (in upper level of the keyboard) and the 'Evaluate Formula' feature under the FORMULAS ribbon also know as Fx.

Using the Fx feature:
With the formula cell active click the 'Evaluate Formula' and click 'Evaluate' repeatedly. With each click Excel shows step by step how the formula is calculated.

Using the F9 function key:
Select just A2:F10=I5 in the formula bar. Press F9 and see an array of TRUE/FALSE. Select MATCH(A1:F1,A1:F1,0) and press F9. You will see {1,2,3,4,5,6}. IF executes MATCH conditional upon A2:F10=I5 being TRUE. The null string "" is optional. I used it to make results of the next F9 step clearer in the formula bar. If you select IF(A2:F10=I5,MATCH(A1:F1,A1:F1,0),"") and press F9 you should see

{"","","","","","";"","","","","","";"","","","","","";"","","","","",6;"","","","","","";"","","","","","";"","","","","","";"","","","","","";"","","","","",""}.

MIN returns the 6 and passes it to INDEX whose first argument is the range of names. INDEX returns the 6th name in that range ... Ivan.

Did this help?

By the way, check the other formulas. INDEX - MATCH might not be your preferred approach to this problem.