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

 

[Krayna]

Hi valmir!

Try using OFFSET.
eg. =OFFSET(MAX(range), 0, 1)

 

[valmir]

Using your formula I get: "this formula contains an error"
This is what I'm using only that it returns only the first maximum value it finds, instead of all existing maximum values.
=INDEX(range;MATCH(MAX(range);range;0))
If possible I would like to have the referenced cell values (if more than one) separated by a comma.

 

[JamesCanale]

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,""))

 

[JamesCanale]

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,""))

I don't think this will work in excel 2010/

 

[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
Based on your example this is exactly what I am looking for, however I don't know why I am getting "#NAME?" as a result.
Could it be because I am using Office 2010?

 

[valmir]

I don't think this will work in excel 2010/

You are right, not working on 2010.

 

[JamesCanale]

Hi James
Based on your example this is exactly what I am looking for, however I don't know why I am getting "#NAME?" as a result.
Could it be because I am using Office 2010?

I have a version problem. You're right - it won't work in 2010.

 

[Fluff]

If you want all the values returned in one cell, you would be better of with VBA, is that an option for you?

 

[valmir]

If you want all the values returned in one cell, you would be better of with VBA, is that an option for you?

Yes, this is a good option too, thanks!