Return the value at position of MaxDate which satisfies a condition

[sarathc]

A_Time	B_Time	C_Time	Condition Time	Result
1/15/2022 6:21:26 AM	1/15/2022 6:21:44 AM	1/15/2022 6:21:13 AM	1/15/2022 6:32:39 AM	B_Time
1/14/2022 7:35:09 PM	3/13/2022 12:08:45 PM	1/15/2022 8:54:37 AM	1/15/2022 9:02:15 AM	C_Time

1) I have 3 columns (A,B and C) which contains some date and time as shown in the table above
2) I want the max of Columns A,B and C, but the condition is that it should be less than or equal to the date and time in Column D
3) And once we get that, it should return the header of the column (like B_Time, C_Time, etc.)

I tried to write a Function for this. Here is my function:

Function ClosestBef(LookupValue As String, TableArray As Range, ReturnArray As Range)
Dim Cell As Range
For Each Cell In TableArray
If Cell.Value > LookupValue Then
Cell.Value = ""
End If
Next Cell
x = Application.Max(TableArray)
y = Application.Match(x, TableArray, 0)
ClosestBef = ReturnArray
End Function

 

[jasonb75]

You don't need to write UDF's for such simple tasks, native functions will be more efficient.

Array confirmed with Ctrl Shift Enter.

=IFERROR(INDEX($A$1:$C$1,IF($A2:$C2=MAX(IF($A2:$C2<=$D2,$A2:$C2)),COLUMN($A$1:$C$1))),"")

 

[sarathc]

I believe Index formula has some limitation when the data is not sorted. That's why I went with custom Function.

The Index formula you gave will give wrong results when anyone of A,B,C is blank and if its not sorted.

 

[jasonb75]

The formula will work fine if the data is not sorted, I had not allowed for blanks as all fields in your example contained dates.

=IFERROR(INDEX($A$1:$C$1,IF($A2:$C2=MAX(IF($A2:$C2<=$D2,IF($A2:$C2<>"",$A2:$C2))),COLUMN($A$1:$C$1))),"")

 

[sarathc]

Sorry to say, its giving wrong results for me. Don't know why. Maybe because of older excel version (2010).

 

[jasonb75]

That was my bad, I missed a bit out that would have caused it to return multiple results where it should only return one.

Both of these should work, if you only have a few rows of data then it will not make much difference, with a lot of data you may find that one calculates faster than the other.

=IFERROR(INDEX($A$1:$C$1,1,MAX(IF($A2:$C2=MAX(IF($A2:$C2<=$D2,IF($A2:$C2<>"",$A2:$C2))),COLUMN($A$1:$C$1)))),"")

=INDEX($A$1:$C$1,MATCH(MAX(IF($A2:$C2<=$D2,$A2:$C2)),$A2:$C2,))