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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]0[/td][td=bgcolor:#5B9BD5]1[/td][td=bgcolor:#5B9BD5]2[/td][td=bgcolor:#5B9BD5]3[/td][td=bgcolor:#5B9BD5]4[/td][td=bgcolor:#5B9BD5]5[/td][td][/td][td=bgcolor:#70AD47]Label[/td][td=bgcolor:#70AD47]Header[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]

4​

[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

4​

[/td][td=bgcolor:#E2EFDA]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]

4​

[/td][td=bgcolor:#DDEBF7]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

7​

[/td][td]

2​

[/td][td]

3​

[/td][td]

3​

[/td][td]

4​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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