# CF match vertical DATENUM list to horizontal dates with a variable interval

I need a CF formula that compares each date in row 3 to the vertical holiday list on worksheet 'Holidays' and colors the cell(s) in row 3 when there is a match for the same WEEKNUM(nn,2) in range 'Holidays'\$D\$5:\$D\$40.  The expected quantity of CF-colored dates should equal the 3-year range of the Holiday list...

Attempted CF formula =WEEKNUM(L\$3,2)=MATCH(WEEKNUM(L\$3,2),Holidays!\$D\$5:\$D\$40,1)... but this does not work correctly. Trying CF TEXT() formulas limits to only 3 correct 'hits' and only if using single-digit values in a series 1 to nn  beneath the date series

sample workbook attached has layout and  several attempts with CF formulas
CF-Vertical-to-Horizon-match.xlsx
Lean process improvement consultantAsked:
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Apply this formula in L3...

``````=COUNTIF(Holidays!\$D\$5:\$D\$40,L3)>=1
``````

And then simply use paint brush to copy it on the rest of the data..

Enclosed your workbook where i have applied this...

Saurabh...
CF-Vertical-to-Horizon-match.xlsx
Commented:
You can use this formula..

``````=COUNTIF(Holidays!\$G\$5:\$G\$40,WEEKNUM(L3,2))>=1
``````

I have used G Column where you have correct weeks mentioned...

Enclosed is your workbook.. Also you were now able to attach workbook here because your workbook name was long if you shorten it up like mine you will be able to attach..

Saurabh...
CF-Vertical-to-Horizoz.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Lean process improvement consultantAuthor Commented:
Thanks Saurabh,  that change works fine!
Commented:
Yw..Always happy to help.. :-)

Saurabh..
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.