How can I get Index Match Max to return multiple results?

[Simon4s]

Teams	Dropped Passes
USA	5
Canada	1
Japan	5

So this formula:

=index(A2:A4,match(max(B2:B4),B2:B4,0))

returns "USA" which is not completely correct.

How can I get it to return:

USA, Japan

 

[AhoyNC]

Try:
Drag formula down as needed.

Book1
	A	B	C	D
1	Teams	Dropped Passes
2	USA	5		USA
3	Canada	1		Japan
4	Japan	5
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=IFERROR(INDEX($A$2:$A$4,AGGREGATE(15,6,(ROW($A$2:$A$4)-ROW($A$2)+1)/($B$2:$B$4=MAX($B$2:$B$4)),ROWS($D$2:D2))),"")

 

[Simon4s]

Try:
Drag formula down as needed.

Book1
	A	B	C	D
1	Teams	Dropped Passes
2	USA	5		USA
3	Canada	1		Japan
4	Japan	5
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=IFERROR(INDEX($A$2:$A$4,AGGREGATE(15,6,(ROW($A$2:$A$4)-ROW($A$2)+1)/($B$2:$B$4=MAX($B$2:$B$4)),ROWS($D$2:D2))),"")

Thanks for the solution. Is it possible to join the two rows into one so that the result appears in one cell?

 

[jasonb75]

Try

=TEXTJOIN(",",,FILTER(A2:A4,B2:B4=MAX(B2:B4)))

 

[Simon4s]

Try

=TEXTJOIN(",",,FILTER(A2:A4,B2:B4=MAX(B2:B4)))

Thanks!! this works perfect

 

[gargamalebarbosa]

Hi
If you can use TEXTJOIN and FILTER functions, your excel version must be 365 or 2021, i think..

 

[AhoyNC]

You need to update your account details if you have Excel 365. The formula I gave you was for 2016 & 2013.