Finding Min/Max for Dynamic Date Range

I have a series of dates (consecutive weeks) each of which falls into one of 4 period. The dates are dynamic based on a given start date. The period assignments are manual inputs that change from use case to use case.

How do I identify the Start and End dates of each period? Please keep in mind the dates, number of periods, and duration of periods change from use case to use case.

I thought about using a MIN and MAX to find the stand and end dates in a given range, but I'm having difficulty writing an INDEX(MATCH,MATCH) formula that will identify the range dynamically from which to select the MAX and MIN.

The attached spreadsheet includes the dates and period assignments. Book1.xlsx

Put in B7, press CTRL+SHIFT+ENTER to confirm the array formula:
=INDEX($B$1:$Q$1,MATCH(MIN(IF($B$2:$Q$2=$A7,COLUMN($B$2:$Q$2))),COLUMN($B$2:$Q$2),0))

Put in C7, press CTRL+SHIFT+ENTER to confirm the array formula:
=INDEX($B$1:$Q$1,MATCH(MAX(IF($B$2:$Q$2=$A7,COLUMN($B$2:$Q$2))),COLUMN($B$2:$Q$2),0))

Select B7:C7, copy down to C10. Format as dates
-------------------------------------------------
Success? Celebrate it, click the little star at the bottom left of my responses

Thank you!!

Trevor's solution worked like a charm!

Hi Martin--

I plugged your solution in and it worked as promised. Can you help me understand how it works? I'm familiar with both INDEX(MATCH) and LOOKUP, but I'm left scratching my head as to why the INDEX(MATCH) returns the smallest date and the LOOKUP returns the largest.

Any insights into how that works?

Thanks!

Originally Posted by martindwilson

B7 =index($b$1:$aP$1,match(a7,$b$2:$aP$2,0))
c7 =LOOKUP(A7,$B$2:$AP$2,$B$1:$AP$1)

Hi Martin--

I plugged your solution in and it worked as promised. Can you help me understand how it works? I'm familiar with both INDEX(MATCH) and LOOKUP, but I'm left scratching my head as to why the INDEX(MATCH) returns the smallest date and the LOOKUP returns the largest.

Any insights into how that works?

Thanks!