Google sheets query: replacing a value in a cell by appending a list of values

Question

I have two sheets.

One is an unpivoted sheet with groups and associated IDs(that is persons in this group) like this:

group	id
Group1	ID1
Group2	ID2
Group2	ID4
Group3	ID1
Group3	ID2
Group3	ID3

The second is rooms and the group with access to that room:

room1	room2	room3
Group1	Group3	Group1
		Group2

I want to get an output like this using the google query in a new sheet. Where the groups have been replaced by their IDs:

room1	room2	room3
ID1	ID1	ID1
	ID2	ID2
	ID3	ID4

My original dataset has over 1000 IDs, 100 groups, and 30 rooms.

Answer 1

Sheet 1:

Sheet 2:

Sheet 3:

Formula for cell A2:

=query({Sheet1!$A:$B},"select Col2 where Col1 matches '"&textjoin("|",true,Sheet2!A2:A)&"' ",0)

Then drag right, eg., cell B2:

=query({Sheet1!$A:$B},"select Col2 where Col1 matches '"&textjoin("|",true,Sheet2!B2:B)&"' ",0)

Cell C2:

=query({Sheet1!$A:$B},"select Col2 where Col1 matches '"&textjoin("|",true,Sheet2!C2:C)&"' ",0)