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


    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!


    • sample.xlsx

      (13.12 kB, downloaded 66 times, last: )