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

Posted on 2011-05-11
Last Modified: 2012-08-13

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

Question by:kieranjcollins
    LVL 43

    Accepted Solution

    Try this formula


    Author Comment

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


    =D$1 instead of >=D$1

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    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.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now