Find the Last Occurrence of cell value after matching date.

[Jabtastic]

Hi All,

I have a duty Rota on Excel. Each month will only show Working Days in a week. So each month will be different. So for example the 1st may be on a Monday or it may be a Friday. The weekends and holidays have been blacked out. I am trying to pull the data into a 4 day rota view. What I want is to match the date and then look for the previous version of cell "Duty Manager" (as the Duty Manager is the same all week).

Right Now I have a Formula:
=IF(COUNTIF($A$10:$A$42,T2)>0,IF($B$10="Duty Manager",INDEX($C$10:$C$41,INT((MATCH(T2,$A$10:$A$41)-1)/7)*7+1),INDEX($C$10:$C$41,INT((MATCH(T2,$A$10:$A$41)-1)/7)*7+2)),IF(COUNTIF($F$10:$F$42,T2)>0,IF($G$10="Duty Manager",INDEX($H$10:$H$41,INT((MATCH(T2,$F$10:$F$41)-1)/7)*7+1),INDEX($H$10:$H$41,INT((MATCH(T2,$F$10:$F$41)-1)/7)*7+2)),IF(COUNTIF($K$10:$K$42,T2)>0,IF($L$10="Duty Manager",INDEX($M$10:$M$41,INT((MATCH(T2,$K$10:$K$41)-1)/7)*7+1),INDEX($M$10:$M$41,INT((MATCH(T2,$K$10:$K$41)-1)/7)*7+2)),"Not Found")))

This seemed to work last month - but not for December or January 2021. I have attached a sample sheet. What I would like is in the table X2:X5 - is to match todays date (7th December 2020) - look up previous occurrence of "Duty Manager" and Return "C Person".

Hope this makes sense?

Jabba

[Bo_Ry]

Please try at X2

=LOOKUP(2,1/(INDEX($A$10:$M$41,,MATCH(TEXT(T2,"mmmm yyyyy"),$A$9:$M$9,))<T2)/(INDEX($B$10:$M$41,,MATCH(TEXT(T2,"mmmm yyyyy"),$A$9:$M$9,))=X$1),INDEX($C$10:$M$41,,MATCH(TEXT(T2,"mmmm yyyyy"),$A$9:$M$9,)))

[Jabtastic]

Hi Bo_Ry,

Perfect!! Thank you for such a quick response. This forum never ceases to amaze me for help and guidance...long may it continue.

Jabba

Please try at X2

=LOOKUP(2,1/(INDEX($A$10:$M$41,,MATCH(TEXT(T2,"mmmm yyyyy"),$A$9:$M$9,))<T2)/(INDEX($B$10:$M$41,,MATCH(TEXT(T2,"mmmm yyyyy"),$A$9:$M$9,))=X$1),INDEX($C$10:$M$41,,MATCH(TEXT(T2,"mmmm yyyyy"),$A$9:$M$9,)))

[Ochimus]

Jabtastic,

Was working through your question when Bo_Ry posted the solution, but thought the attached might still have some use for you?

Cols A , E and I show both the weekday and date.
As B3 says the Year has to start in April, put in C3 1/4/(whatever year).
That sets the Month sequence in Col N, which populates the DropLists in B5, F5 and J5.
What you select generates the dates in Row 11, with the remaining dates cascading from the first entry, until the date is later than the end of the month, when cell goes blank:

=IFERROR(IF(A11+1>EOMONTH(B$5,0),"",A11+1),"")

CF for the Saturday is =WEEKDAY(A11,1)=7 and for Sunday =WEEKDAY(A11,1)=1, applying to all three tables.

Hope you find it makes life simpler and even speeds up the file?

Ochimus