Solved

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

Posted on 2011-05-11
281 Views

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
0
Question by:kieranjcollins

LVL 43

Accepted Solution

Try this formula

=IF(MAX(IF((Data!\$B\$1:\$B\$1111<=\$B3)*(Data!\$B\$1:\$B\$1111>=\$A3)*(Data!\$A\$1:\$A\$1111>=C\$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>=C\$1),Data!\$B\$1:\$B\$1111,0)))
0

Author Comment

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)))

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

Featured Post

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!