How to display the corresponding name of the highest score in Excel?
Supposing, I have range of data which contains two columns – name column and the corresponding score column, now, I want to get the name of the person who scored highest. Are there any good ways to deal with this problem quickly in Excel?
To retrieve the name of the person who scored the highest, the following formulas can help you to get the output.
Please enter this formula: =INDEX(A2:A14,MATCH(MAX(B2:B14),B2:B14,FALSE),)&" Scored "&MAX(B2:B14) into a blank cell where you want to display the name, and then press Enter key to return the result as follows:
1. In the above formula, A2:A14 is the name list which you want to get the name from, and B2:B14 is the score list.
2. The above formula only can get the first name if there are more than one names having the same highest scores, to get all the names who got the highest score, the following array formula may do you a favor.
Enter this formula:
=INDEX($A$2:$A$14,SMALL(IF($B$2:$B$14=MAX($B$2:$B$14),ROW($B$2:$B$14)-1),ROW(B2)-1)), and then press Ctrl + Shift + Enter keys together to display the first name, then select the formula cell and drag the fill handle down until error value appears, all names who got the highest score are displayed as below screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 days agoHow would you show two names if the high scores match?
- To post as a guest, your comment is unpublished.· 5 months agoHow would you get the matching numbers with the corresponding names to display horizontally?
- To post as a guest, your comment is unpublished.· 2 years agoHi - How would i do the above formula with an added criteria? For example if there were a column that flagged "audited" or "full time" - how could i return the name of the student and their score for the Auditing student with the highest score?
See my data below - I want to return both the brand name and the market share of category for the brand with the highest share in a given country
Geography Category Brand Name Share of category
China Hot Cereals Quaker 24.3
China Hot Cereals Seamild 20.2
China Hot Cereals Nesvita 5.7
China Hot Cereals Aces 3.5
China Hot Cereals Gold Roast 2.9
China Hot Cereals Zaozaomai 1.0
China Hot Cereals Yashili 0.9
China Hot Cereals Black Cow 0.2
China Hot Cereals Yashili -
China Hot Cereals Yashili -
China Hot Cereals Others 41.3
China Hot Cereals Total 100.0
Hong Kong, China Hot Cereals Quaker 81.3
Hong Kong, China Hot Cereals Ideal -
Hong Kong, China Hot Cereals Private label -
Hong Kong, China Hot Cereals Others 18.7
Hong Kong, China Hot Cereals Total 100.0
India Hot Cereals Quaker 26.7
India Hot Cereals Saffola 17.2
India Hot Cereals Kellogg's 14.4
India Hot Cereals Horlicks Oats 12.8
India Hot Cereals Bagrry's 5.5
India Hot Cereals Express Foods 1.6
India Hot Cereals Avee's 0.6
India Hot Cereals Mohun's 0.5
India Hot Cereals Champion -
India Hot Cereals Others 20.7
India Hot Cereals Total 100.0
Indonesia Hot Cereals Quaker 80.4
Indonesia Hot Cereals Simba 13.0
Indonesia Hot Cereals Energen 4.9
Indonesia Hot Cereals Captain Oats -
Indonesia Hot Cereals Others 1.7
Indonesia Hot Cereals Total 100.0
- To post as a guest, your comment is unpublished.· 2 years agoIs there a way to display the top score within a range: inf-90,89.9999-80, etc.for the second command?