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

Ryan_V

New Member
Joined
Mar 25, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

1616697224003.png
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 25, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thank you!

Headers in Row 4 are just text.

Mr.Excel Test.xlsx
ABCDEFGHIJKLMN
1
2START MONTHMAY
3
4JANUARYFEBRUARYMARCHAPRILMAYJUNEJULYAUGUSTSEPTEMBEROCTOBERNOVEMBERDECEMBER
5REDALLALLALLALLALLALLALLALLALLALLALLALL
6YELLOWAUSAUSAUS
7BLUEUK
8GREENEOEOEOEOEOEO
9WHITEAMAMAMAM
10ORANGETHTH
11BLACKCHCHCH
12
13
14
15
16RESULTS I'D LIKE
17NEXT MTHNEXT 2 MTHNEXT 3 MTHNEXT 4 MTH
18REDMAYJUNEJULYAUGUST
19YELLOWJUNEOCTOBER
20BLUEDECEMBER
21GREENMAYJULYSEPTEMBERNOVEMBER
22WHITEMAYAUGUSTNOVEMBER
23ORANGE
24BLACKMAYJULYOCTOBER
test
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 25, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1
2START MONTHMay
3
4JanFebMarAprMayJunJulAugSepOctNovDec
5REDALLALLALLALLALLALLALLALLALLALLALLALL
6YELLOWAUSAUSAUS
7BLUEUK
8GREENEOEOEOEOEOEO
9WHITEAMAMAMAM
10ORANGETHTH
11BLACKCHCHCH
12
13
14
15
16RESULTS I'D LIKE
17NEXT MTHNEXT 2 MTHNEXT 3 MTHNEXT 4 MTH
18REDMayJunJulAug
19YELLOWJunOct
20BLUEDec
21GREENMayJulSepNov
22WHITEMayAugNov
23ORANGE 
24BLACKMayJulOct
25
Result
Cell Formulas
RangeFormula
C18:F18,C24:E24,C22:E22,C21:F21,C20,C23,C19:D19C18=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.
 
Solution

Ryan_V

New Member
Joined
Mar 25, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
This is AMAZING and works perfectly! Thank you for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,691
Messages
5,637,846
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top