Efficient way to find last vector element that is a member of a set

Question

I have a spreadsheet where column A lists the days of a month (1-Jan, 2-Jan, ...), column B lists the corresponding days of the week (Fr, Sa, Su, Mo, ...), and column C contains various notes that can be "holiday" or "vacation". I want to compute in a cell the (index of) the last "working" day of the month (i.e., the last week day in the month that is not a holiday or vacation day).

If I wanted to simply find the last Friday in the month I could do:

LOOKUP(2,1/ ($B$2:$B$32<>"Fr")),ROW($B$2:$B$32))-1))

To get multiple days of the week, I can "OR" together multiple vector-scalar comparisons using addition:

LOOKUP(2,1/ (($B$2:$B$32="Mo")+...+($B$2:$B$32="Fr")),ROW($B$2:$B$32))-1))

To add the constrain that they can't be holidays or vacation days, I can "AND" this together with more comparisons using multiplication:

LOOKUP(2,1/ ((($B$2:$B$32="Mo")+...+($B$2:$B$32="Fr"))*(($C$2:$C$32<>"holiday")*($C$2:$C$32<>"vacation"))),ROW($B$2:$B$32))-1))

This works but is very large and unwieldy. Is there a way to do this more elegantly that compares each vector only once to a list, something like the following?

LOOKUP(2,1/ (IN($B$2:$B$32, {"Mo","Tu", "We", "Th", "Fr"})*NOTIN($C$2:$C$32, {"holiday","vacation"})),ROW($B$2:$B$32))-1))

Answer 1

Assuming data similar to:

The following formulas should return the last working day of the month:

O365

=WORKDAY(EOMONTH(A2,0)+1,-1,FILTER(tblDates[Dates],(tblDates[Notes]="holiday")+(tblDates[Notes]="vacation")))

earlier versions without FILTER function

=WORKDAY(EOMONTH(A2,0)+1,-1,AGGREGATE(15,6,1/((tblDates[Notes]="holiday")+(tblDates[Notes]="vacation"))*tblDates[Dates],ROW(INDEX($A:$A,1):INDEX($A:$A,SUM(COUNTIF(tblDates[Notes],{"vacation","holiday"}))))))

The formulas use the WORKDAY function and, starting with the first of the next month, subtract one (1) workday. The WORKDAY function automatically ignores weekends, and has a HOLIDAYS argument to skip those, also.