Return result based on date range

  • Hi there


    I am working with historical commodity prices. I need to look at a date and return a result based 2 criteria.

    1. The (commodity) season

    2. A date


    Here is a sample of the data:




    For example, if I had a piece of data from the 11/12 season (field A), with a date of 11/02/2012 (field B), I want the result to be "WH 2012".


    Can anyone help with this?


    Thanks, Nick

  • Attaching a small sample file would help

    The basics would be

    =INDEX(D1:D10,MATCH(1,(A1:A10="11/12")*(B1:B10<=YOUR_DATE)*(C1:C10>=YOUR_DATE),0))


    To be committed as an array formula with Ctrl+Shift+Enter

  • Thanks Pecoflyer - I've attached a sample as I don't think that quite captures it.


    In the attached:

    • I want a formula to populate yellow - which I have manually populated in the sample;
    • The reference data is in orange, with the answer I need taken from column M;
    • I need the formula to consider the season in green and the date in blue. You will notice that row 4 and row 5 have the same date but different seasons - and therefore different results in yellow.

    Thanks for your help!