https://docs.google.com/spreadsheets/d/1qjCOu1wLYG6btsxI1bVf0AO_XGIiZNRE0m3J7CYGF1A/edit?usp=sharing

In this particular sheet, I am seeing if I can make a formula so that it finds the desired lesson codes entered on the side and finds them in the overview, returning the lesson code & room, and teacher in the periods that it finds the lesson code in.

For example, if 7EN06 was typed in, it would search each period in Overview and return the teacher responsible in the corrisponding Period if found.

CodePudding user response：

=transpose(query({{Overview!B3:B16,Overview!A3:A16};{Overview!C3:C16,Overview!A3:A16};{Overview!D3:D16,Overview!A3:A16}},"select * where Col1 contains '"&G4&"'"))