Help with Workday.intl

Hi,

I have a drop down list in cell A1 with various currency Pairs.. (E.G. USD/THB)

I have a table in Column C:D where Column C has Currencies (E.g. USD, JPY etc) & Column D has Holidays (Excluding Saturdays & Sundays)

On cell F1, I am trying to build a formula, =WORKDAY.INTL(TODAY(),30,1,D2:D997) however, from the C column, it should only consider the Left Side of the Pair USD/THB and take into consideration Holidays of only that currency.
Sample file attached!

Try this. I used an INDEX MATCH to extract the relevant holidays into a separate column (green) and used a named range to refer dynamically to that column. It might also help if you were to add Named ranges for columsn B & C as well, to make them dynamic, too.

Originally Posted by Glenn Kennedy

Try this. I used an INDEX MATCH to extract the relevant holidays into a separate column (green) and used a named range to refer dynamically to that column. It might also help if you were to add Named ranges for columsn B & C as well, to make them dynamic, too.

Hi, that is working, but I forgot to tell that I am not looking to use Helper Columns/Cells.. Extremely sorry for the late input..

Assuming your currencies are all grouped together (you'd have to sort columns C and D, using C as the key) you can use an OFFSET function within your existing formula to get the correct holiday range like this

=WORKDAY.INTL(TODAY(),7,1,OFFSET(D$1,MATCH(LEFT(A$1,3),C$2:C$999,0),0,COUNTIF(C$2:C$999,LEFT(A$1,3))))

OK. But this may work out to be a bit slow. I had to sort your data by country and then by date to get it working.

DLL - that's clever!!

....but this way works without any sorting.....

=WORKDAY.INTL(TODAY(),7,1,INDEX((C$2:C$999=LEFT(A$1,3))*D$2:D$999,0))

Hi, thank you DLL & Glenn for your amazing formulas.. Based on what I'v tested so far, it is giving me the right output dates..

Marking the thread as solved and adding reps..

Cheers!

Thanks for the Reputation that you have added. It's much appreciated.