Dynamic Header Lookup / Index Match and then Lookup sequential values based on non-blank criteria

[Ryan_V]

Hello,

I'm wondering if anyone has any thoughts on how to create a dynamic lookup calc to help solve my problem below. I've mocked up a scaled down version of the file I'm working with.
Using Excel for Microsoft 365.

Goal:
I have a list of colors that are produced during various months of the calendar year, displayed along an x/y axis.
I.e. Red is produced every month, but Yellow is only produced in March, June, and October.
The production month is noted by having a non-blank cell, corresponding to the intersection of the color & month.
I'd like to know the earliest month that each color is going to be produced based on the "start month" which is identified by cell B2. This cell will be a formula changing based on a TODAY() calc.
Once identifying the earliest month, I'd like to know the following 3 months that the color will be produced.

Results I'd like:
Can be seen in green section. So cells C18:F24 would be formulas, looking at the data in C5:N11.
Using "May" as the "Start Month" for my current example
Red will show next month being May, next 2 being June, next 3 being July, next 4 being August.
Yellow's earliest month in relation to my "start month" in B2 would be June
Orange's month's have all passed therefore it would be blank.
etc.

Appreciate any help/suggestions!
Thank you

 

[Fluff]

Hi & welcome to MrExcel.
Are the headers in row 4 actual dates or just text?

Also please post some sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Ryan_V]

Thank you!

Headers in Row 4 are just text.

Mr.Excel Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	START MONTH	MAY
3
4			JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	JULY	AUGUST	SEPTEMBER	OCTOBER	NOVEMBER	DECEMBER
5		RED	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL
6		YELLOW			AUS			AUS				AUS
7		BLUE												UK
8		GREEN	EO		EO		EO		EO		EO		EO
9		WHITE		AM			AM			AM			AM
10		ORANGE		TH		TH
11		BLACK					CH		CH			CH
12
13
14
15
16		RESULTS I'D LIKE
17			NEXT MTH	NEXT 2 MTH	NEXT 3 MTH	NEXT 4 MTH
18		RED	MAY	JUNE	JULY	AUGUST
19		YELLOW	JUNE	OCTOBER
20		BLUE	DECEMBER
21		GREEN	MAY	JULY	SEPTEMBER	NOVEMBER
22		WHITE	MAY	AUGUST	NOVEMBER
23		ORANGE
24		BLACK	MAY	JULY	OCTOBER
test

 

[Fluff]

Would you be willing to use dates & use a cell format to just use mmm?
Also do you have the LET function in your version of Excel?

 

[Ryan_V]

Yes, I can definitely use dates and the cell format to mmm
I do have the LET function

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	START MONTH	May
3
4			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
5		RED	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL	ALL
6		YELLOW			AUS			AUS				AUS
7		BLUE												UK
8		GREEN	EO		EO		EO		EO		EO		EO
9		WHITE		AM			AM			AM			AM
10		ORANGE		TH		TH
11		BLACK					CH		CH			CH
12
13
14
15
16		RESULTS I'D LIKE
17			NEXT MTH	NEXT 2 MTH	NEXT 3 MTH	NEXT 4 MTH
18		RED	May	Jun	Jul	Aug
19		YELLOW	Jun	Oct
20		BLUE	Dec
21		GREEN	May	Jul	Sep	Nov
22		WHITE	May	Aug	Nov
23		ORANGE
24		BLACK	May	Jul	Oct
25
Result

Cell Formulas
Range	Formula
C18:F18,C24:E24,C22:E22,C21:F21,C20,C23,C19:D19	C18	=LET(Ary,TEXT(FILTER($C$4:$N$4,(FILTER($C$5:$N$11,$B$5:$B$11=B18)<>"")*($C$4:$N$4>=$B$2),""),"mmm"),INDEX(Ary,SEQUENCE(,MIN(COLUMNS(Ary),4))))
Dynamic array formulas.

 

[Ryan_V]

This is AMAZING and works perfectly! Thank you for your help!

 

[Fluff]

You're welcome & thanks for the feedback.