How to lookup corresponding row and column header based on max value in table

[JSM9992]

Hello,

I'm trying to pull the column and row header values that correspond with the max value in the table. In this case the value of "20" in cell E3. I would like the corresponding row header "2" to show up in cell B8 and corresponding column header "4" to be in cell B9. The values will change whenever I update the table so the max value will move each time I update. I appreciate the help.

Excel 2012
	A	B	C	D	E	F
1		1	2	3	4	5
2	1	2	10	5	4	3
3	2	4	5	11	20	16
4	3	3	2	1	8	6
5	4	15	7	12	3	2
6	5	9	5	13	4	17
7
8
9
Sheet1

 

[MARK858]

For the cell contents try...

Excel Workbook
	A	B	C	D	E	F
1		6	7	8	9	12
2	1	2	10	5	4	3
3	2	4	6	11	20	16
4	3	3	2	1	8	6
5	4	15	45	12	37	2
6	5	9	5	35	4	17
7
8		4
9		7
Sheet3

 

[MARK858]

Or probably better...

Excel Workbook
	A	B	C	D	E	F
1		6	7	8	9	12
2	1	2	10	5	4	3
3	2	4	6	11	20	16
4	3	3	2	1	8	6
5	4	15	45	12	37	2
6	5	9	5	35	4	17
7
8		4
9		7
Sheet3

 

[cyrilbrd]

I could have duplicates but the chances are very slim. The numbers I'm calculating will be in the thousands with decimals points. Side note, I used the array formula suggested by Rossa and it worked. The only thing is it appears to be calculating the actual row or column number and not the value I have in the orange highlighted section (thus the reason for having the -1 at the end of the formula). The headers in orange will actually be much larger numbers than 1-5 and I believe if they're random and not sequential that the formula might not work. However, it worked on the table I presented so all is good. Thanks for the responses!

Noted, here is an alternative to MARK858's solution(s)

Given the following:

Excel 2013
	A	B	C	D	E	F
1		first	second	third	fourth	last
2	one	2	3	5	4	16
3	two	4	5	11	20	16
4	three	3	2	1	8	6
5	four	15	7	12	3	2
6	five	9	20	13	4	17
7
8
9
10		if multiple	row header	column header
11	max	20	two	fourth
12			five	second
JSM9992

Formula in B11 is a simple =MAX(B2:F6)
Formula in C11 copied down till needed is =IF(ROWS(C$11:C11)>COUNTIF($B$2:$F$6,B$11),"",INDEX(A$2:A$6,SMALL(IF($B$2:$F$6=B$11,ROW($B$2:$F$6)-ROW($B$2)+1),ROWS(C$11:C11)))) Ctrl + Shift + Enter not just Enter on a PC or Command + Return on a MAC.
Formula in D11 copied down as needed is =IF(C11="","",INDEX(B$1:F$1,MATCH(B$11,INDEX(B$2:F$6,MATCH(C11,A$2:A$6,0),0),0)))

Such would give you all data if multiple entries were found.

 

[JSM9992]

The options from MARK858 and cyrilbrd both worked perfectly. Although chances of duplicates are minimal, it's nice to have the formula just in case. Thanks again everybody.

 

[cyrilbrd]

The options from MARK858 and cyrilbrd both worked perfectly. Although chances of duplicates are minimal, it's nice to have the formula just in case. Thanks again everybody.

Most welcome. Thanks for the feedback.