Find max value, including more than one match, if any

[valmir]

Hello everyone
I'm using the formula =MAX(range) to find the maximum value from a range. However, as you know, this formula only returns the first maximum value and in case there is more than one maximum value, it will return the first value it finds.
In addition to that, I want that single or multiple maximum values to reference to adjacent cell(s) on the left.
Thanks

 

[Fluff]

Ok how about

Function valmir(MaxRng As Range, TxtRng As Range) As String
   Dim i As Long
   Dim Mx As Double
   
   Mx = Application.Max(MaxRng)
   For i = 1 To MaxRng.Rows.Count
      If MaxRng(i).Value = Mx Then valmir = valmir & ", " & TxtRng(i)
   Next i
   valmir = Mid(valmir, 3)
End Function

used like

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Jamie		6		silly, that, good
3	is		7
4	a		8
5	silly		9
6	goose		8
7	that		9
8	eats		6
9	good		9
10	food		4
Import

Cell Formulas
Range	Formula
E2	E2	=valmir(C2:C10,A2:A10)

 

[valmir]

MrExcelPlayground.xlsx
	B	C	D	E
2	Jamie	6		silly, that, good
3	is	7
4	a	8
5	silly	9
6	goose	8
7	that	9
8	eats	6
9	good	9
10	food	4
Sheet15

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN(", ",TRUE, IF(C2:C10=MAX(C2:C10),B2:B10,""))

Hi! I'm now using excel 2016 but when I try to use your formula, I'm getting this message:

 

[Fluff]

The 2016 version of Excel does not have the textjoin function.

 

[valmir]

MrExcelPlayground.xlsx
	B	C	D	E
2	Jamie	6		silly, that, good
3	is	7
4	a	8
5	silly	9
6	goose	8
7	that	9
8	eats	6
9	good	9
10	food	4
Sheet15

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN(", ",TRUE, IF(C2:C10=MAX(C2:C10),B2:B10,""))

Hi James. I upgraded to Excel 365 and your formula worked to perfection. Now I would like some help in another variant of that formula. The difference is that I want to have the top five maximum values across 16 columns, while returning all matching values, if any, of each of the top five. I also want the values to reference to the respective adjacent cells on the left.

 

[JamesCanale]

Hi James. I upgraded to Excel 365 and your formula worked to perfection. Now I would like some help in another variant of that formula. The difference is that I want to have the top five maximum values across 16 columns, while returning all matching values, if any, of each of the top five. I also want the values to reference to the respective adjacent cells on the left.

You might post this as another question. Few might look at a solved question. Some things that would be important... seeing your spreadsheet layout, would it be the top five in the total data set or the top five of the max of each of the 16 columns?

 

[valmir]

You might post this as another question. Few might look at a solved question. Some things that would be important... seeing your spreadsheet layout, would it be the top five in the total data set or the top five of the max of each of the 16 columns?

Hi James. Thanks for your reply, actually I had to resort to an online paid service and I was able to get the solution from them. Thanks again!