CODEWITHSUNDEEP
excelspreadsheet
Zack
Zack

Reputation: 13972

Cell function for determining another cell value in the same row returned by max function (excel)

I don't really know how to ask this via google, so I'll just do a description here.

I am using excel, and I have a table like so

Pushups

A          B    C
6/16/2016  45   35
6/17/2016  47   37
etc...

Basic pushups per day document. I know how to get my "best" day...

=max(B2:...)
=max(C2:...)

How do I programatically get the date for the best day? Essentially, I need to be able to take the index of the row that Max has selected, and then get the value from another column... column A. So, whatever the max is, I need a formula to get the value from column A in the same row.

Should be an easy one I think, just don't know enough excel.

Upvotes: 0

Views: 96

Answers (2)

Konstantin Ivanov
Konstantin Ivanov

Reputation: 410

 =INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0))

OR

=INDEX(A1:A4;MATCH(MAX(B1:B4);B1:B4;0))

depending on Excel version and localization

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

Use the code below (modify Column B to whatever Column you are looking for the Maximum Value)

Option Explicit

Sub Date_of_Max()

Dim Row_Max         As Long
Dim Rng             As Range    

' modify your range here to whatever Column you want    
Set Rng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
Row_Max = WorksheetFunction.Index(Rng, WorksheetFunction.Match(WorksheetFunction.Max(Rng), Rng, 0)).Row
MsgBox Range("A" & Row_Max)

End Sub

Upvotes: 1

Related Questions