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

JSM9992

New Member
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.

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">1</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFC000;;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFC000;;">3</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFC000;;">4</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFC000;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">1</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;">10</td><td style="text-align: right;border-top: 1px solid black;;">5</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">2</td><td style="text-align: right;border-left: 1px solid black;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">11</td><td style="text-align: right;;">20</td><td style="text-align: right;border-right: 1px solid black;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">3</td><td style="text-align: right;border-left: 1px solid black;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td><td style="text-align: right;border-right: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">4</td><td style="text-align: right;border-left: 1px solid black;;">15</td><td style="text-align: right;;">7</td><td style="text-align: right;;">12</td><td style="text-align: right;;">3</td><td style="text-align: right;border-right: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">5</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;">9</td><td style="text-align: right;border-bottom: 1px solid black;;">5</td><td style="text-align: right;border-bottom: 1px solid black;;">13</td><td style="text-align: right;border-bottom: 1px solid black;;">4</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Roderick_E

Well-known Member
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
 

uzaname

New Member
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
 

Blade Hunter

Well-known Member
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:

MARK858

Well-known Member
Possibly B8 copied down.

Excel Workbook
ABCDEF
112345
21210543
3245112016
4332186
541571232
659513417
7
8
94
104
111
125
Sheet3
 

rossa

Board Regular
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
 

Joyner

Well-known Member
rossa looks good, you forgot to mention this is an array formula and must be entered with Ctrl+Shift+Enter, not just enter.
 

MARK858

Well-known Member
Yes, rosser (with the addition of the statement Joyner made) it works well (especially as I totally miss-read the question :()
 

cyrilbrd

Well-known Member
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:
<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">1</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">3</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">4</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFCC00;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">row header</td><td style=";">column header</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">max</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">JSM9992</p><br /><br />
 
Last edited:

JSM9992

New Member
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!
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top