Index and Match on Multiple Columns with Duplicate Values

[wsr429]

Hi, I have a spreadsheet which I created on Excel for Mac (version 16.16.27). It contains 5 Names (A2:A6). Each Name has 5 Rounds in B2:F6.

I used the Large function to get the 3 largest Rounds:

I2 contains the largest Round (=LARGE($B2:$F6,1)) which = 5.6 (found in E3).
I3 contains the second largest Round (=LARGE($B2:$F6,2)) which also = 5.6 (found in D6).
I4 contains the third largest Round (=LARGE($B2:$F6,3)) which = 5.56 (found in B4).

Now I want to identify the Name associated with each of the 3 largest Rounds.

H2 accurately identifies the Name as Alex as the person with the highest Round (=5.6) using this formula:
=INDEX($A$2:$A$6,MATCH(1,MMULT(--($B$2:$F$6=I2),TRANSPOSE(COLUMN($B$2:$F$6)^0)),0))

H3 also returns the Name as Alex using the below formula. However, it should have returned the Name as Jacob since 2nd highest Round of 5.6 belongs to him (D6).
=INDEX($A$2:$A$6,MATCH(1,MMULT(--($B$2:$F$6=I3),TRANSPOSE(COLUMN($B$2:$F$6)^0)),0))

H4 accurately identifies the Name as Brian as the person with the 3rd highest Round (=5.56) using this formula:
=INDEX($A$2:$A$6,MATCH(1,MMULT(--($B$2:$F$6=I4),TRANSPOSE(COLUMN($B$2:$F$6)^0)),0))

Obviously, my formula doesn’t account for duplicate Rounds. Is there a way to correct this shortcoming?

I adapted the formula for the Name lookup from my web search, but I don't understand it's details.

Thanks for your help,

William

PS - attached is a screen print of my spreadsheet

 

[AhoyNC]

Try:
This is an array formula and you may need to use COMMAND+RETURN (I think that's what you use on a Mac), but with the AGGREGATE function that may not be needed.

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Round 1	Round 2	Round 3	Round 4	Round 5		Names	Highest Rounds
2	Aiden	5.51	5.5	4.97	4.96	4.11		Jacob	5.6
3	Alex	4.92	5.23	5.13	5.6	5.47		Alex	5.6
4	Brian	5.56	3.02	5.15	4.93	4.92		Brian	5.56
5	Christian	4.61	5.19	5.53	5.23	5.04
6	Jacob	5.49	5.12	5.6	5.37	4.25
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=INDEX($A$2:$A$6,AGGREGATE(14,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/($B$2:$F$6=I2),COUNTIF($I$2:I2,I2)))
I2	I2	=LARGE($B$2:$F$6,1)
I3	I3	=LARGE($B$2:$F$6,2)
I4	I4	=LARGE($B$2:$F$6,3)

 

[wsr429]

Thank you so much, AhoyNC! Your solution worked perfectly. I appreciate your prompt and accurate reply. Thanks again! William

​

 

[AhoyNC]

You're welcome. Thanks for the feedback.
I forgot to tell you that you can just drag the formula down as needed.

 

[wsr429]

Thanks for your additional instruction. I was able to apply your formula to my spreadsheet. I double checked the results and now I have the data that I wanted. You are an Excel wiz and I am blessed to be the recipient of your expertise. Thank you for being willing to assist me. I would have never been able to figure this out on my own. Thanks again!