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

#### FSAEStudent

##### New Member
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
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
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
another way with Power Query

 0 1 2 3 4 5 Label Header Value 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]``````