INDIRECT dependent on multiple points of reference

I am working on a lunch schedule, that's bound with shift rota (pics attached).

What I want to achieve is automation on lunch rota in the following way:

• user picks their name from dropdown (B column on lunch, being sourced from rota's F column)
• shift column is then cross-picked from =TODAY() (B4 on lunch sheet) + shift rota's current day (3rd row on another sheet)
• then, depending on the shift, an hour slot is assigned to the user (for each shift a specific time-span e.g. shift 7-15 > slot = 12-13)
• it's a 40 people team, so I need to divide the two half-hour slots between them (20 slots 12.00-12.30 + 20 slots 12.30-13.00) - we can't have them lunching and breaking all at the same time :D

I found some instructions on how to create an INDIRECT functions, however, I am not sure how to make it dependent on three cells.

Also, I found problems working between online and local versions of Excel - some functions seem to break. In the end, I need it to work online, so that's my current working enviroment.

rota.png
lunch.png

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Which 'latest' version do you have? Update your profile with Excel 2019 or MS365 as appropriate.