match date name and specific shift and return date in other cell

[godlev]

Hello friends
i have a team schedule which we use to track the team absence
from time to time i want to check which dates exactly someone from the team was in specific absence which we mark with C
on the tab schedule we have column A with the names
Row 1 and 2 with the dates
the crossing cells are the corresponding shifts


i wanted to make a array formula to list only C entries for certain person in compensations tab but i was not able to compile something to work properly.

i would appreciate your help if you help me find formula or VBA to fill in the yellow cells in compensations tab with the dates which the corresponding name on top was assigned to C shift

[Jacc]

Try this array formula in the leftmost cell, Ctrl + Shift + Enter and then fill right:

Please Login or Register  to view this content.

[Jacc]

Oh, I just realized I moved cells around. Well, in case you can't figure it out, here is the workbook as well.
I also added number to firstname in the column.

[godlev]

thank you very much Jacc
i figured it out when i saw the combination of the names were not matching
actually my formula was very similar but i was always messing up with the last part

Please Login or Register  to view this content.

thank you so much this will save me a lot of manual checks
if you come to bulgaria call me ill buy you a beer

[Jacc]

You are welcome! Beer is always good, new types are always interesting.
It also reminds me that I need to go travel more.