Index/Match over multiple tabs

Ok, good idea. I'll try to explain.

So on tabs "SS", "Math", "Sci", etc i have 3 important columns. Column D has a name, Column E has Room Number, and Column G has Period (period in the form of 1ABCD or 2AC or 8B, for a 4 day rotation).

I want to transform this onto a separate tab that that shows Room Usage.

On that sheet I would have room numbers down the left had side. On the top row i would have 1A 1B 1C 1D 2A 2B etc. I want the teachers names to appear in the columns that match room w period.

ie. on the tab "SS"

D	E	F	G
Joe	300	Math	1AC

On the room usage tab i'd like to see next to 300 the columns with 1A and 1C to have the name Joe in them. The formula, in my mind would search for a match on ALL sheets with Room number and Period and return teacher name if there is a match.

THE BIG ISSUE is i would like the formula to search columns D, E, and G on alllll the tabs.
Also, not sure how I would have it associate "1AC" with "1A" AND "1C".

Hope that helps. You're right, i believe sometimes i get fixated on formulas I know rather than exploring other options.