Link to home
Start Free TrialLog in
Avatar of kieranjcollins
kieranjcollinsFlag for Ireland

asked on

Array formula - max date from a range for a specific value



I have attached part of a file I am working on

I have a list of dates and sales assistants on 1 sheet

What I am looking to do is write a formula so that my array formula:
=IF(MAX(IF((Data!B:B<=B6)*(Data!B:B>=A6),Data!B:B,0))=0,"",MAX(IF((Data!B:B<=B6)*(Data!B:B>=A6),Data!B:B,0)))
will also look at data values on another column as a condition for doing the max range formula

e,g, I would like Cell C3 to show max date range values for Sales Assist 1 etc


Any help greatly appreciated
Kieran

 Array-formula-help.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kieranjcollins

ASKER

I made a small tweak to your formula to get it working

=IF(MAX(IF((Data!$B$1:$B$1111<=$B3)*(Data!$B$1:$B$1111>=$A3)*(Data!$A$1:$A$1111=D$1),Data!$B$1:$B$1111,0))=0,"",MAX(IF((Data!$B$1:$B$1111<=$B3)*(Data!$B$1:$B$1111>=$A3)*(Data!$A$1:$A$1111=D$1),Data!$B$1:$B$1111,0)))

=D$1 instead of >=D$1

But other than that, your help was awesome, 500 points to you