Locating largest value and return it's heading

Question

    V1   V2   V3

A   1    5     9

B   2    2     6

C   7    8     4

Hi Experts,

I need support to find the largest value (whole range) in this table and then return it's location. For example, largest value is 9 and the result should also return A V3.

Thank you

Answer 1

you can do this with an Array Formula (so you have to enter it with Ctrl+Shift+Enter:

=OFFSET($A$1,MAX(IF(B2:D4=MAX(B2:D4),ROW(B2:D4),0))-1,0)&" "&OFFSET($A$1,0,MAX(IF(B2:D4=MAX(B2:D4),COLUMN(B2:D4),0))-1)

• The array bit is in the IF(B2:D4=MAX(B2:D4),ROW(B2:D4),0) - it will return the ROW in which the value equals the MAX in the range
• we then get the MAX of that (we need one value to work with for the offset)
• using offset we can then get the value
• and join it all together with the same, but with the COLUMN

The addresses are assuming that your example was at the top of your sheet!

EDIT: WITH THE RANKING - uses LARGE() function to get the nth largest, rather than MAX

Answer 2

The following is a mish-mash of something I did a while a back. It's not very elegant but it should work:

Sub test()

Dim h1 As Range
Dim h2 As Range
Dim myRange As Range
Dim c As Range
Dim maxNum As Integer
Dim output As String
Dim offsetCol As Integer
Dim offsetRow As Integer

Set h1 = Range("A2:A4")
Set h2 = Range("B1:D1")
Set myRange = Range("B2:D4")

offsetCol = myRange.Column - 1
offsetRow = myRange.Row - 1

maxNum = Application.WorksheetFunction.Max(myRange)

For Each c In myRange
    If c = maxNum Then output = h1.Cells(c.Row - offsetRow, 1) & " " & h2.Cells(1, c.Column - offsetCol): Exit For
Next c

Debug.Print output

End Sub

Answer 3

Say we have:

.

Then:

Sub Finder()
    Dim tabl As Range, headr As Range, mx As Variant
    Dim wf As WorksheetFunction, r As Range
    Set wf = Application.WorksheetFunction
    Set tbl = Range("C5:E7")
    Set headr = Range("C4:E4")
    mx = wf.Max(tbl)
    Set r = tbl.Find(What:=mx)
    v = Intersect(headr, r.EntireColumn).Value
    MsgBox v
End Sub

EDIT#1

This version returns the concatenation of the column header and the row ID:

Sub Finder()
    Dim tabl As Range, headr As Range, mx As Variant
    Dim wf As WorksheetFunction, r As Range, colr As Range
    Set wf = Application.WorksheetFunction
    Set tbl = Range("C5:E7")
    Set headr = Range("C4:E4")
    Set colr = Range("B5:B7")
    mx = wf.Max(tbl)
    Set r = tbl.Find(What:=mx)
    v = Intersect(headr, r.EntireColumn).Value
    v = v & " " & Intersect(colr, r.EntireRow).Value
    MsgBox v
End Sub