Formula to pull in Quarter from a reference sheet

I have created a reference sheet called Quarter Reference Sheet that has the quarters based on a start and end date:

I have another sheet where I want to create a formula that pulls in the "Quarter" column above based on if the "Completed Date" column falls somewhere between the Start Date and End Date in the reference sheet. Example, if the Completed Date column has a date of April 1, 2020, I want the formula to look at the information in the Start Date and End Date columns in the reference sheet and pull in the value of the appropriate matching row in the Quarter column - in this example, Q1 2020.

Is this an index/match formula? If so, does anyone have a formula I could try using here that might work?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ah. Ok. Sorry about that. Let's see... Give this one a try...

    =INDEX({Table Sheet Quarter Column}, MATCH(MAX(COLLECT({Table Sheet Start Date Column}, {Table Sheet Start Date Column}, @cell <= [Completed Date]@row)), {Table Sheet Start Date Column}, 0))


    The basic rundown of how this works is we pull the largest date that is less than or equal to the [Completed Date]. Then we match on that to determine which row from the Quarter column we need to pull.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!