Formula to return the max value and corresponding column header

[shady moustafa]

Hi All,

I am looking for a way to search for the maximum value in a range and returns the name of the column which includes the identified value. I am attaching a screenshot of an example table, and the desired output below the table.

The output of the exercise will be the following:

(Cell B15 includes the criteria)
1- Cell C15 to include The maximum value from the range
2 Cell D15 to include the name of the column

Thank you

 

[JamesCanale]

This will handle a tie reasonably well.

MrExcelPlayground5.xlsx
	A	B	C	D	E
1
2
3			Jan	Feb	Mar
4		A	1	0	0
5		B	0	2	0
6		C	0	0	3
7		A	0	100	20
8		B	2	4	6
9		C	100	10	10
10		A	0	0	0
11		B	50	20	30
12		C	10	30	10
13
14
15	Output	A, C	100	Feb, Jan
Sheet27

Cell Formulas
Range	Formula
B15	B15	=TEXTJOIN(", ",TRUE,IF(INDEX(C4:E12,SEQUENCE(ROWS(C4:E12)),SEQUENCE(1,COLUMNS(C4:E12)))=C15,B4:B12,""))
C15	C15	=MAX(C4:E12)
D15	D15	=TEXTJOIN(", ",TRUE,IF(INDEX(C4:E12,SEQUENCE(ROWS(C4:E12)),SEQUENCE(1,COLUMNS(C4:E12)))=C15,C3:E3,""))

 

[JamesCanale]

Reading this again, I think this is what you want:

MrExcelPlayground5.xlsx
	A	B	C	D	E
1
2
3			Jan	Feb	Mar
4		A	1	0	0
5		B	0	2	0
6		C	0	0	3
7		A	0	100	20
8		B	2	4	6
9		C	100	10	10
10		A	0	0	100
11		B	50	20	30
12		C	10	30	10
13
14
15	Output	A	100	Feb, Mar
Sheet27 (2)

Cell Formulas
Range	Formula
C15	C15	=MAX(FILTER(C4:E12,B4:B12=B15))
D15	D15	=TEXTJOIN(", ",TRUE,IF(INDEX(FILTER(C4:E12,B4:B12=B15),SEQUENCE(ROWS(FILTER(C4:E12,B4:B12=B15))),SEQUENCE(1,COLUMNS(FILTER(C4:E12,B4:B12=B15))))=C15,C3:E3,""))