Return max value based on value in another cell

[junkforhr]

I have the following dummy table.

Value of Header1 will be in cell A18. Value of Header2 will be in cell A19 and value Header3 will be in cell A20.

I have about 20 headers and I want the highest number in the range based on what is in the relevant cell. The order of the headers may change, so for one month it may not have all 20 options.

Desired result

Header1	13​
Header2	7​
Header3	19​

Row Labels	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Grand Total
11/04/2023	13​		18​	6​	7​			1​	45​
12/04/2023	7​	2​	14​	5​	3​	2​			33​
13/04/2023	7​	3​	9​	4​	5​			1​	29​
14/04/2023	2​		5​	2​	8​				17​
17/04/2023	7​	1​	17​	4​	6​		3​		38​
18/04/2023	13​	1​	14​	2​	3​	1​			34​
19/04/2023	4​	1​	16​	8​	3​				32​
20/04/2023	6​	2​	7​	3​	1​				19​
21/04/2023	10​	1​	10​	3​	4​				28​
24/04/2023	5​	3​	9​	8​	5​		1​		31​
26/04/2023	4​		15​	2​	7​	1​	2​		31​
27/04/2023	5​		7​	1​	2​	1​	1​		17​
28/04/2023	7​		11​	1​	6​			1​	26​
1/05/2023	11​	1​	17​	2​	8​				39​
2/05/2023	9​		15​	1​	5​				30​
3/05/2023	5​		14​	2​	3​				24​
4/05/2023	7​	1​	12​	7​	3​		1​		31​
5/05/2023	13​		12​	1​			2​		28​
8/05/2023	6​		6​	8​	5​	1​	1​		27​
9/05/2023	4​		14​	1​	3​				22​
10/05/2023	12​	1​	4​	3​	8​				28​
11/05/2023	5​	1​	9​		2​				17​
12/05/2023	4​		9​	2​	5​				20​
15/05/2023	6​		19​	3​	8​	1​	2​		39​
16/05/2023	6​		12​	2​	2​		1​	1​	24​
17/05/2023	8​	1​	14​	2​				1​	26​
18/05/2023	12​		15​	4​	12​				43​
19/05/2023	11​	7​	8​	12​	5​				43​

 

[DanteAmor]

Hi @junkforhr , I hope you're doing well.
Thanks for posting on the forum.

Try the following formula. You will have to adjust it to the range where you have the data and where you have the headers.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1
18	Header1	13
19	Header2	7
20	Header3	19
21
22	Row Labels	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Grand Total
23	11/04/2023	13		18	6	7			1	45
24	12/04/2023	7	2	14	5	3	2			33
25	13/04/2023	7	3	9	4	5			1	29
26	14/04/2023	2		5	2	8				17
27	17/04/2023	7	1	17	4	6		3		38
28	18/04/2023	13	1	14	2	3	1			34
29	19/04/2023	4	1	16	8	3				32
30	20/04/2023	6	2	7	3	1				19
31	21/04/2023	10	1	10	3	4				28
32	24/04/2023	5	3	9	8	5		1		31
33	26/04/2023	4		15	2	7	1	2		31
34	27/04/2023	5		7	1	2	1	1		17
35	28/04/2023	7		11	1	6			1	26
36	01/05/2023	11	1	17	2	8				39
37	02/05/2023	9		15	1	5				30
38	03/05/2023	5		14	2	3				24
39	04/05/2023	7	1	12	7	3		1		31
40	05/05/2023	13		12	1			2		28
41	08/05/2023	6		6	8	5	1	1		27
42	09/05/2023	4		14	1	3				22
43	10/05/2023	12	1	4	3	8				28
44	11/05/2023	5	1	9		2				17
45	12/05/2023	4		9	2	5				20
46	15/05/2023	6		19	3	8	1	2		39
47	16/05/2023	6		12	2	2		1	1	24
48	17/05/2023	8	1	14	2				1	26
49	18/05/2023	12		15	4	12				43
50	19/05/2023	11	7	8	12	5				43
Hoja3

Cell Formulas
Range	Formula
B18:B20	B18	=MAX(INDEX($A$22:$J$50,0,MATCH(A18,$A$22:$J$22,0)))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[junkforhr]

Hi @junkforhr , I hope you're doing well.
Thanks for posting on the forum.

Try the following formula. You will have to adjust it to the range where you have the data and where you have the headers.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1
18	Header1	13
19	Header2	7
20	Header3	19
21
22	Row Labels	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Grand Total
23	11/04/2023	13		18	6	7			1	45
24	12/04/2023	7	2	14	5	3	2			33
25	13/04/2023	7	3	9	4	5			1	29
26	14/04/2023	2		5	2	8				17
27	17/04/2023	7	1	17	4	6		3		38
28	18/04/2023	13	1	14	2	3	1			34
29	19/04/2023	4	1	16	8	3				32
30	20/04/2023	6	2	7	3	1				19
31	21/04/2023	10	1	10	3	4				28
32	24/04/2023	5	3	9	8	5		1		31
33	26/04/2023	4		15	2	7	1	2		31
34	27/04/2023	5		7	1	2	1	1		17
35	28/04/2023	7		11	1	6			1	26
36	01/05/2023	11	1	17	2	8				39
37	02/05/2023	9		15	1	5				30
38	03/05/2023	5		14	2	3				24
39	04/05/2023	7	1	12	7	3		1		31
40	05/05/2023	13		12	1			2		28
41	08/05/2023	6		6	8	5	1	1		27
42	09/05/2023	4		14	1	3				22
43	10/05/2023	12	1	4	3	8				28
44	11/05/2023	5	1	9		2				17
45	12/05/2023	4		9	2	5				20
46	15/05/2023	6		19	3	8	1	2		39
47	16/05/2023	6		12	2	2		1	1	24
48	17/05/2023	8	1	14	2				1	26
49	18/05/2023	12		15	4	12				43
50	19/05/2023	11	7	8	12	5				43
Hoja3

Cell Formulas
Range	Formula
B18:B20	B18	=MAX(INDEX($A$22:$J$50,0,MATCH(A18,$A$22:$J$22,0)))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Thanks for that, it's exactly what I was after