How to generate filtered Vstack based on column list + Repeating rows based on Countif

Hi all,
Hope someone can help please.
I have attached three Files:
File 1 lists a column of Room ID's (differentiated by plan and elevation) which each need specific types of Products.
File 2 lists all Possible Room ID's and what each Product every Room ID needs.
File 3 partially shows the data desired (but I came up with this through basic filter function and copy/paste)


I would like excel to create an expanded version of these two files which lists the products needed in every Room ID in each plan and elevation.

1.) I would like to generate Vstack/s of the data in File2, filtered successively on all the Room IDs listed in File1.
Starting from the top row 2 (Q30D-EM-BATH), I would like Excel to create a vstack of File2 Filtered for RoomID:BATH,
then on row 3 (Q30D-MO-BATH) filtered for RoomID: BATH,
then row 4 (Q30D-EM-BATHALT) filtered for RoomID: BATHALT,
then row 5 (Q30D-TA-BATHPRIMALT) filtered for RoomID: BATHPRIMALT,
and so on...
I would like Excel to list the results in successive arrays / vstacks.

2.) Then right beside this long list is another array/ multiple vstack where Excel exactly first repeats Row2 in File1 (Including Plan, Elev and Room ID data) according to the number of times the RoomID in Row2-File1 appears in the RoomID-File2.
Starting from the top, by evaluating that RoomID:Bath in the top row of File1 (Q30D-EM-BATH) appears 9 times in RoomID-File2, Excel creates a list repeating Q30D-EM-Bath 9 times.
Then evaluating row 3 in File1 (Q30D-MO-Bath), adds Q30D-MO-Bath in the next 9 rows,
then evaluating row 4 in File1 (Q30D-EM-Bathalt), adds / repeats Q30D-EM-Bathalt in the next 9 rows,
then evaluating row 5 in File1 (Q30D-TA-BATHPRIMALT), adds Q30D-TA-BATHPRIMALT in the next 16 rows,


3.) File 3 partially shows the data desired where 1 and 2 are combined (but I came up with this through basic filter function and copy/paste).

Hope someone here can help!

Thank you!