Find position of max value in excel

Question

I have a 2*3 matrix ranging from cells C2:E3. I need to find the row number of the maximum number

Can someone suggest a VBA code for the same I tried to use index and match but not getting proper results

Answer 1

Try the code below (explanation inside the code comments):

Option Explicit

Sub FindMaxValRow()

Dim Rng As Range
Dim MaxCell As Range
Dim MaxVal As Long

Set Rng = Range("C2:E3")
MaxVal = WorksheetFunction.Max(Rng)

' use the Find function to get the Row number
Set MaxCell = Rng.Find(what:=MaxVal, LookIn:=xlValues)

MsgBox "Maximum value found at row " & MaxCell.Row

End Sub

Answer 2

VBA-free solution:

Assuming that the table is in $C$2:$E$3, an array formula (type Ctrl+Shift+Enter instead of just Enter)

=MAX((MAX($C$2:$E$3)=$C$2:$E$3)*ROW($C$2:$E$3))

returns the maximal row number, in which the maximal element is found.

To get the minimal row number, in which the maximal element is found, we can use this array formula:

=ROW($C$3:$E$3)-MAX((MAX($C$2:$E$3)=$C$2:$E$3)*(ROW($C$3:$E$3)-ROW($C$2:$E$3)))