How to Locate a value and display the match item

[wpm7113]

Hello,

I have a question in excel that I hope you can help.

I have spreadsheet that contact a scoresheet that keep track of all the player score on a weekly basis, and each week, I have to locate for the highest score and display the name of the player.

I use the function Large(), however I don't how to locate for the player name.

Kindly advice. Here is the sample data

[Richard Buttrey]

Hi, and welcome to the forum.

First use a helper column E and enter the formula:

Please Login or Register  to view this content.

in E4 and copy down

Now in A12 enter the formula:

Please Login or Register  to view this content.

and copy down.

HTH

[NBVC]

In case there are ties, you need a formula that is a little more complicated than a simple Index/Match formula...

Try in A12:

Please Login or Register  to view this content.

This is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear around the formula... then copy it down.

Note: If you adjust the ranges, you will need to reconfirm with the CSE keys.

Sample attached.

[wpm7113]

Thanks a lot and it works. !!!

I have another question. which I forget to ask earlier.

How can I put a condition in the Large() function where I can differcient between gender then the large number?

Sample attachment.

Many thanks and this really help me a lot

[NBVC]

Try:

=LARGE(IF($B$4:$B$9=B13,C$4:$E$9),1)

confirmed with CTRL+SHIFT+ENTER and copied down.

you can also use Max()

=MAX(IF($B$4:$B$9=B13,C$4:$E$9))

also CSE entered

[wpm7113]

It works. Thank you very much!!!

[wpm7113]

Hello Again.

I just found a problem with the matching and hope that you could help again.

I encounter problem with the matching if the score of the player are the same and for some reason the gender does not match.

Please see the attached files.

Please advice and help. many thanks again.

[NBVC]

In A13:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down

in A18:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

I have included the condition of gender....