Iteration with IFNA and VLOOKUP

[dmitryt]

Hi! I have sheet with following data:

WorkDates column contains dates, but only working days, public holidays are excluded.
I need to enter Date1 and Date2 and amount of average daily payment. For example:
Date1 is 01.01.2021 and Date2 is 03.01.2021, I can (Date3-Date1)x$100 = $200.
I am using VLOOKUP to find DateIndex from the table, but the is a problem if enter Date that is out of range of the table. For example Date1 is Sunday and there is not DateIndex for it. I've tried to use IFNA, but all it helps - to change NA value.
Is there any option without VBA to do Iteration (+1) IF NA returned and do it unless real DateIndex returned?
Some kind of Nested IFNA?

Logic example screenshot

 

[Fluff]

If you tried to lookup the 16th or 17 of Jan 2021 what should it return 5 or 6 or something else?

 

[dmitryt]

it should return DateIndex of first Date that is available in the table.
If we put 16.01.2021 or 17.01.2021 in Date 1, it should return DateIndex 6 for Date 18.01.2021.

 

[Fluff]

Ok, how about

=VLOOKUP(WORKDAY(B2-1,1),Workdates!A2:B742,2,0)

 

[dmitryt]

Ok, how about

=VLOOKUP(WORKDAY(B2-1,1),Workdates!A2:B742,2,0)

it does not help, because workdays are not only Sut-Sun, but different national local holidays too. For example if put 05.09.2021 - it returns NA.
I was thinking about IFNA go to next date, but how to increment next date in this case?

 

[dmitryt]

Maybe it will be good to add Non-Working days in main table, add indexes and value 0 for Non-working days or 1 for Working days, then return 2 indexes and Sum Values. Will try.

 

[Fluff]

it does not help, because workdays are not only Sut-Sun, but different national local holidays too.

In that case you need to add the holiday dates into the workday function.

 

[dmitryt]

OK, Finally I did it with

=SUMPRODUCT((A2:A1095>=H2)*(A2:A1095<=H3)*(C2:C1095=1))

but as I mentioned above - I enhanced main table - added All days into it, added Value 1 for working days and 0 for non-working days.

 

[Fluff]

You don't need to do that, you can just add the holiday list into the workday function

=VLOOKUP(WORKDAY(B2-1,1,Holidays),Workdates!A2:B742,2,0)

just replace the word Holidays with the range that holds you holiday dates.