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

[FSAEStudent]

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]

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

 

[AlphaFrog]

Do a search for Excel Reverse two way lookup

Here's a good one
https://www.mrexcel.com/forum/excel...e-two-way-lookup-post1706232.html#post1706232

 

[Paul Ked]

https://www.mrexcel.com/forum/excel...e-two-way-lookup-post1706232.html#post1706232

Great thread.

 

[jasonb75]

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]

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​

[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]