return cell from another sheet if it contains string in column-CodePudding

I am working on a school rooming timetable system. I am finding a formula which finds if a particular room is used during a specific period by which class and teacher.
There is a staff overview with clean data of which class and room they are in, alongside preferred results.
https://docs.google.com/spreadsheets/d/1H9Q8K9KBLgqCsvVZ_ekqsLPbZKeMzvldDnyI-KerOD4/edit?usp=sharing

For example, for on the overview, Monday Period 1 (Column B) Staff 1 uses room BG01 for the class 07MA01. Thus on the room overview, it shows that in Period 1, Staff 1 uses BG01 for 07MA01.

CodePudding user response：

Use filter() and regexmatch(), like this:

=iferror( 
  textjoin( "; ", true, 
    filter( 
      regexreplace('Staff Overview'!B$3:B, "@. ", "") & "(" & trim('Staff Overview'!$A$3:$A) & ")", 
      regexmatch('Staff Overview'!B$3:B, $A3) 
    ) 
  ),  
  "FREE" 
)