Excel公式 - 如何扩展数据

my brain is melting and I can't figure this out. I have a long list of of rooms of which each type has a code in worksheet 1. These codes are duplicated where room types are similar, so a reception will always be BBA, data capture - BCJ, etc. On the second worksheet I can see what equipment needs to be bought for each room type and each item line has the same codes, BBA, AGA, etc along with quantities needed per room. How can I expand this data on the third spreadsheet showing all rooms with all the equipment needed?my brain is melting and I can't figure this out