Retun emplyee name and team based on criteria from a table

[akhileshgs]

Hello All,

Could you please help me to get the list of employee names and their team extracted who will have 'Office' on a specific date when I manually enter a specific date in the yellow highlighted cell?
The expected result is highlighted in green.


https://drive.google.com/file/d/1s7s...w?usp=drivesdk

Thank you in advance

[alansidman]

Please upload your file to this site. Many members are reluctant to open third party sites.

[akhileshgs]

I am not able to attach files to the post. I tried it many times.
After that only i took this long route of uploading the file in drive and then posted the link here
Attachment 676331

[torachan]

Missed the big yellow banner at the head of the page ?

[akhileshgs]

Got it. Thanks :D

I sware.. I did those steps and were not seeing the attachment then.

[Limor_OP]

Hi,
I did it with the offset formula:

This one to find the name:
=IFERROR(OFFSET($A$1,SMALL(IF(($C$2:$U$7="office")*($C$1:$U$1=$C$8),ROW($F$2:$F$7)),(ROWS($A$1:A1)))-1,0),"")

And this to find the team:
=IFERROR(OFFSET($A$1,SMALL(IF(($C$2:$U$7="office")*($C$1:$U$1=$C$8),ROW($F$2:$F$7)),(ROWS($A$1:A1)))-1,1),"")

Please see attached.