How do I get Excel to return a value based on the max value in a table?

FSAEStudent

New Member
Joined
Aug 20, 2019
Messages
7
0 1 2 3 4 5
1 1 2 3 4 5
3 6 7 8 9 0
5 1 2 3 4 6
7 2 3 3 4 2

I have a table as such with rows and columns in bold. I know the max value within this table is 9. How can I get Excel to return "3" and "4"?
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
434
Code:
Sub FindMax()
    Dim Rng As Range, Val As Long, Loc As Range
    Set Rng = Range("A1:E7")
    Val = WorksheetFunction.Max(Rng)
    Set Loc = Rng.Find(what:=Val, LookIn:=xlValues)
    MsgBox "Maximum value found at row " & Loc.Row & ", column " & Loc.Column
End Sub
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,883
Office Version
2019
Platform
Windows
With newer excel versions, perhaps this with wrapped text (array confirmed with Ctrl Shift Enter)

=TEXTJOIN(CHAR(10),1,IF($B$2:$F$5=MAX($B$2:$F$5),$A$2:$A$5&"&"&$B$1:$F$1,""))
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,206
another way with Power Query

012345LabelHeaderValue
1​
1​
2​
3​
4​
5​
3​
4​
9​
3​
6​
7​
8​
9​
0​
5​
1​
2​
3​
4​
6​
7​
2​
3​
3​
4​
2​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"0"}, "Attribute", "Value"),
    Type = Table.TransformColumnTypes(Unpivot,{{"0", Int64.Type}, {"Attribute", Int64.Type}, {"Value", Int64.Type}}),
    Filter = Table.SelectRows(Type, each ([Value] = List.Max(Type[Value]))),
    Rename = Table.RenameColumns(Filter,{{"0", "Label"}, {"Attribute", "Header"}})
in
    Rename[/SIZE]
 

Forum statistics

Threads
1,077,662
Messages
5,335,561
Members
399,024
Latest member
rokcel389

Some videos you may like

This Week's Hot Topics

Top