Bring a range of holiday dates into workday arrayformula

user_19377887 0
Viewed 76

I'm using WORKDAY in my sheet to find the next working day for an array of dates. Each date has a country variable which determines the list of holidays (stored elsewhere) passed to WORKDAY.

I have been able to use FILTER to achieve this, but only when I pass a single country name to it as a condition. Ideally, I want pass the whole range of country names to it so that I can use an ARRAYFORMULA. After I try that, I hit the mismatched range sizes error.

Here's a link to a sample workbook: https://docs.google.com/spreadsheets/d/1zQiOmPxOjpkV5g05vm-1ReI-9m_AltabQamXw2iUKrI/edit?usp=sharing

country variable to be used in workday arrayformula

holidays data stored here

Any suggestions?

google-sheets-formula google-sheets
1 Answers

Yes its mismatched range sizes

On D2 Use This =ARRAYFORMULA(IF(C2:C="",,WORKDAY(A2:A,1,FILTER(K2:K,J2:J=A2:A)) ))

And on C2 Use this formula =IF(A2="",,WORKDAY(A2,1,FILTER($K$2:$K$8,$J$2:$J$8=B2))) and drag down to the bottom of the sheet.

And you will have this result

user_195296940
Related