CODEWITHSUNDEEP
excel-formula
MsAgentM
MsAgentM

Reputation: 153

Max function with Index/match formua does not return max value

I have data with a list of dates and times. There are multiple matching entries for most dates. I want to return the earliest time and the lastest time corresponding to each date. I need to do this without using array formulas.

I have used a max and Min version of the following formula:

MAX((INDEX('2025'!P:P,MATCH(Tables!BZ5,'2025'!O:O,0),0))))

This formula just returns the firs time associated with the date

MAX(INDEX(('2025'!O:O=Tables!BZ4)*'2025'!P:P,0))

This formula returns a #VALUE! Error

MAX(INDEX(('2025'!O2:O450=Tables!BZ4)*'2025'!P2:P450,0))

This formula returns a 0.

--Expected Result--

Data:

Dates    Time    
5/9/19   3:30 PM    
5/9/19   11:30 AM    
5/9/19   2:00 PM

Expected results

Date       Earliest time      Latest time    
5/9/19     11:30 AM           3:30 PM

Based on other board, formulas above worked for others, but please let me know if there is a syntax or other issue I am missing.

Upvotes: 0

Views: 151

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

If you have Office 365 use MAXIFS()/MINIFS():

=MAXIFS('2025'!P:P,'2025'!O:O,Tables!BZ4)
=MINIFS('2025'!P:P,'2025'!O:O,Tables!BZ4)

If not then you can use AGGREGATE

=AGGREGATE(14,7,'2025'!P2:P450/('2025'!O2:O450=Tables!BZ4),1)
=AGGREGATE(15,7,'2025'!P2:P450/('2025'!O2:O450=Tables!BZ4),1)

If your Excel is pre 2010 then you will need an array formula MAX(IFS()):

=MAX(IF('2025'!O2:O450=Tables!BZ4,'2025'!P2:P450))
=MIN(IF('2025'!O2:O450=Tables!BZ4,'2025'!P2:P450))

These last two must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Upvotes: 2

Related Questions