Find highest value in row and return Row header

[Castor]

Hi all,

Did a search and couldn't locate an already existing post. I am looking for a way to search for the highest value in a row and return that row's header - I see many for returning the column header but not the row. For example..

Test.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
7			July 2021	August 2021	September 2021	October 2021	November 2021	December 2021	January 2022	February 2022	March 2022	April 2022	May 2022	June 2022	GRAND TOTAL
8	Employee Name	Black	2	27	1	6	8	23	32	21	14	12	15	9	170
9		Blue	4			1				1	3	4	3	1	17
10		Green	7				3			4	5	2	9	3	33
11		Orange		6	30	5	4	4		7	21	8	9	6	100
12		Red	1									2			3
Sheet3

Cell Formulas
Range	Formula
Q8:Q12	Q8	=SUM(E8:P8)

Basically, I want to take the row with the highest grand total value, and return the row header of that row - in this case the highest grand total is 170, so I want to return 'Black"

Any help is appreciated!

Thanks

 

[jeffreybrown]

How about

=INDEX(D8:D12,MATCH(MAX(Q8:Q12),Q8:Q12,0))

 

[Fluff]

What should happen if two (or more) rows have the same total?

 

[Castor]

How about

=INDEX(D8:D12,MATCH(MAX(Q8:Q12),Q8:Q12,0))

This is working - I realized - i have multiple employees - if I have multiple employees how can I return the highest value corresponding to each employee? Can this be done with a single formula that I could jsut click and drag down? Like a formula with two 'match' values? for example

Copy of 2022_08_10-All Provider Radiology Info Monthly By DOS updated EM-99999 report-DBT (006).xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
7			July 2021	August 2021	September 2021	October 2021	November 2021	December 2021	January 2022	February 2022	March 2022	April 2022	May 2022	June 2022	GRAND TOTAL
8	Joe	Black	2	27	1	6	8	23	32	21	14	12	15	9	170
9		Blue	4			1				1	3	4	3	1	17
10		Green	7				3			4	5	2	9	3	33
11		Orange		6	30	5	4	4		7	21	8	9	6	100
12		Red	1									2			3
13
14
15			July 2021	August 2021	September 2021	October 2021	November 2021	December 2021	January 2022	February 2022	March 2022	April 2022	May 2022	June 2022	GRAND TOTAL
16	Tom	Black		2	1		2	2	2	1		5	3	1	19
17		Blue	1	2				1				5	5	2	16
18		Green		3	15	3		5		6	2	2	5	3	44
19		Orange		10			1	28	7	7	1	10	3	12	79
20		Red		1	1		1	3				2	2	2	12
21
22
23			July 2021	August 2021	September 2021	October 2021	November 2021	December 2021	January 2022	February 2022	March 2022	April 2022	May 2022	June 2022	GRAND TOTAL
24	Mike	Black			2					1	2	2	6		13
25		Blue			1					1	2		2		6
26		Green									1				1
27		Orange		2	16	1	2			8	11	4	9		53
28		Red				2	5	27	17	10	5	9	15		90
29
30
31	Informational Purpose:	Value should be…
32	Joe	Black
33	Tom	Orange
34	Mike	Red
Sheet3

Cell Formulas
Range	Formula
Q24:Q28,Q16:Q20,Q8:Q12	Q8	=SUM(E8:P8)

What should happen if two (or more) rows have the same total?

In these cases we could just return the color with the first highest value when the colors are sorted alphabetically.

 

[Fluff]

With your version of Excel you are probably better off just changing the ranges for each row in D32:D34