Return different values in a cell from different column ranges, based on today`s date

Dear Forum,

I have this on-call tracking sheet (see attachment) where I need to return different values in a cell from different column ranges, based on today`s date.
So the goal would be to see which person from column D has on call duty today (based on column B and C) in which group (column A)
The Result should look somehow like this:
Picture1.png

Hi
Use these formula to create a help column
=ROW($A2:$A625)*--($B2:$B625<=$J$1)*--($C2:$C625>=$J$1)*--($D2:$D625<>"")
where J1 is today()
See the fileBook1 (3).xlsx

Hi
Yes. You can put on other Sheet.
I correct the formula, so the names are grouped.

Formula:

Please Login or Register  to view this content.

See the file Book1 (4).xlsx and the Sheet "Today"

Hi Jose

this is amazing. I really appreciate your help.
Have a nice day

Alex

Hi Alex
Thank you for the feedback.

Do not forget to mark this thread as solved.

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved