Combine functions to return the right values from a matrix

Hello Everyone,

I have a monthly calendar with all the staff for for every day of the month (MATRIX), and I need to put in formulated template (RETURN VALUES FROM THE MATRIX), just the staff working in one specific day, just changing the date of the template.

The columns in the MATRIX: staff name, job position, shift, unit, and one column for each day of the month. If the employee doesn't work some day, I leave the cell blank; if they work that day, I type the unit code, and other codes could be included like vacation, holiday, leave of absence, or sick.

What I expect in the section called "RETURN VALUES FROM THE MATRIX" is that, if I select a date on the cell assigned, all the staff for that day should be populated automatically on the specific area below, showing all other codes as well if there is someone on holiday, vacation, sick, etc, on that day.

The functions I tried to combine initially were Index & Match but obviously doesn't work; I guess I have to combine Index & Aggregate, or Filter but at this point I'm stuck.

I would really appreciate your help.

Thank you so much in advance.

something like in n5
=FILTER($E$5:$E$22,OFFSET(E5,0,MATCH(N4,F4:J4,0),18,1)=M6)

but you will have to hard code the vacation holiday and sick

Thank you so much davsth; I had to fix some files but it works. The only thing that I don't know how to fix now is that the number of rows with new employees can increasee; how can I make the height dynamic?

Thank you so mnuch CheeseSandwich!! it works perfect!! Really appreciate your help on this matter. I have a question though, what if I convert the matrix in a table; everytime I add a new employee are the formulas being adjusted automatically? Again thank you so much for your help!

M6=transpose(unique(b5:b22))

m7=iferror(filter($e$5:$e$100,(index($f$5:$j$100,,match($n$4,$f$4:$j$4,0))=m$6)*(index($f$5:$j$100,,match($n$4,$f$4:$j$4,0))>0)),"")

r7=iferror(filter($e$5:$e$100,(index($f$5:$j$100,,match($n$4,$f$4:$j$4,0))=r$6)*(index($f$5:$j$100,,match($n$4,$f$4:$j$4,0))>0)),"")

Thank you so much TMS!

Thank you so much for the update CheeseSandwich; it works perfect! really appreciate your help!

Ciao CARACALLA; grazie per la tua proposta! It is really a great idea for a different type of report. Thank you so much!
I was thinking that could be very interesting to include another two filters following your idea.
Would you mind to consider having three filters (Day, Shift, Position)?

Thank you so much TMS; I'm a new user and I didn't know about that. Thank you so much!

M9=iferror(filter($e$5:$e$100,(index($f$5:$j$100,,match($n$4,$f$4:$j$4,0))=m$8)*(index($f$5:$j$100,,match($n$4,$f$4:$j$4,0))>0)*(index($b$5:$d$100,,match($m$5,$b$4:$d$4,0))=$n$5)*(index($b$5:$d$100,,match($m$6,$b$4:$d$4,0))=$n$6)),"")

r9=iferror(filter($e$5:$e$100,(index($f$5:$j$100,,match($n$4,$f$4:$j$4,0))=r$8)*(index($f$5:$j$100,,match($n$4,$f$4:$j$4,0))>0)*(index($b$5:$d$100,,match($m$5,$b$4:$d$4,0))=$n$5)*(index($b$5:$d$100,,match($m$6,$b$4:$d$4,0))=$n$6)),"")