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

JSM9992

New Member
Joined
Jan 29, 2015
Messages
3
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
ABCDEF
112345
21210543
3245112016
4332186
541571232
659513417
7
8
9
Sheet1
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I'm a bit confused, wouldn't B8 be the result of max of actual Excel row 2? So, max is 10 so B8 would equal 1 and B9 would equal 2.
If this is what you meant, the code for B9 - F9

Code:
=MATCH(MAX($B2:$F2),$A$2:$F$2,0)-1
=MATCH(MAX($B3:$F3),$A$3:$F$3,0)-1
=MATCH(MAX($B4:$F4),$A$4:$F$4,0)-1
=MATCH(MAX($B5:$F5),$A$5:$F$5,0)-1
=MATCH(MAX($B6:$F6),$A$6:$F$6,0)-1
For B8-F8 it would simply be 1 thru 5 just like the headers
 
Upvote 0
Here is VBA code that should do it. I am not sure how to do it with formulas.

Code:
Sub Test()
    Dim x As Range
    For Each x In [B2:F6]
        If x.Value = Application.WorksheetFunction.Max([B2:F6]) Then
            [B8].Value = Cells(x.Row, 1).Value
            [B9].Value = Cells(1, x.Column).Value
        End If
    Next x
End Sub
 
Upvote 0
There will be a MUCH better way to do this but I enjoyed messing about with it :)

Excel 2010
ABCDEF
112345
21210543
3245112016
4332186
541571232
659513417
7
82
94

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B8=OFFSET(A2,((LOOKUP(MAX(B2:F6),B2:F6))/(ROW(B6)-ROW(B2)+1)-(TRUNC((LOOKUP(MAX(B2:F6),B2:F6))/(ROW(B6)-ROW(B2)+1))))*(ROW(B6)-ROW(B2)+1),0)
B9=OFFSET(B1,0,TRUNC((LOOKUP(MAX(B2:F6),B2:F6))/(ROW(B6)-ROW(B2)+1)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


Edit: Strangely enough it doesn't seem to work the way I thought it would.

Oh well, had fun messing around :)
 
Last edited:
Upvote 0
Possibly B8 copied down.

Excel Workbook
ABCDEF
112345
21210543
3245112016
4332186
541571232
659513417
7
82
94
104
111
125
Sheet3
 
Upvote 0
Someone else can let you know if there are any weaknesses with the below, I think it's ok, but one issue might be that you might have a duplicate of the maximum value.

For the Row
MIN(IF($B$2:$F$6=MAX($B$2:$F$6),ROW($B$2:$B$6)))-1

For the Column
MIN(IF($B$2:$F$6=MAX($B$2:$F$6),COLUMN($B$2:$F$6)))-1
 
Upvote 0
rossa looks good, you forgot to mention this is an array formula and must be entered with Ctrl+Shift+Enter, not just enter.
 
Upvote 0
Yes, rosser (with the addition of the statement Joyner made) it works well (especially as I totally miss-read the question :()
 
Upvote 0
Someone else can let you know if there are any weaknesses with the below, I think it's ok, but one issue might be that you might have a duplicate of the maximum value.

For the Row
MIN(IF($B$2:$F$6=MAX($B$2:$F$6),ROW($B$2:$B$6)))-1

For the Column
MIN(IF($B$2:$F$6=MAX($B$2:$F$6),COLUMN($B$2:$F$6)))-1
For the OP, could you have duplicates of a MAX value within your table? Would you want then to return ALL results or just the first one?
Example:

Excel 2013
ABCDEF
112345
212205416
3245112016
4332186
541571232
659513417
7
8
9
10row headercolumn header
11max2012
1224
JSM9992

 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top