Array formula help: "Who is working today?"

Hi everyone, been struggling to work my head around this and would really appreciate any help if possible!

I'm attempting to make a spread-sheet that merges my work-rota with a daily duty list; you plug in a date, and it will return a list of staff members who are scheduled for that day.

I think I have found a few kind of examples that approach this, such as http://eimagine.com/how-to-return-mu...ch-or-vlookup/
but I am struggling to work out how to adapt this to my specific example.

Would anyone be able to explain this for me, if it is even possible to do?


Example:

example.png

So, you type into B16 the date you want to check the rota for, and in cells A17 and below it will return a list of any staff that either have a blank cell, or their cell contains "HD" or "LS" on that specific date (so searches cells C3:C11 for this information, based on the date you typed in.).

Thanks in advance for any response,
Eiden

Try this.
ARRAY formula in A17, then drag down.

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

You're both geniuses - sorry for the late response, but thank you both so much.

If you don't mind helping again, I'd much appreciate it...

I'm trying to now make a front page "organisational sheet" - i.e As well as just changing the date, you can select which month to view by typing into C16.

However I'm struggling to get my adapted formula to reference the correct sheets (You type January into C16, it'll look at January... you get the idea) - I've tried using the INDIRECT function, but I'm not sure how to correctly apply this.

Any bright ideas? Or should I just stick with keeping an organisational section on each page?

Again JeteMc, thanks so much for your help! Definitely learnt a few more functions I thought I'd never have to use before.