Need an Excel Formula

Hello, I'm likely having trouble finding a formula for this because I can't articulate properly what I want but here goes...

I have two worksheets - 1 and 2 let's call them:
On sheet 1 I have 2 columns named "room" and "fname". Under column "room" there will be entries 1a,1b, 2a, 2b... through to 28b but in a random order (do not want to sort). Under column "fname" there will be a name which corresponds to the number in the room column.

On Sheet 2 I want to create a formula that will match in order the name next to the entries 1a, 1b, 2a, 2b.... in order.

To give you some background - Sheet 1 column "fname" will have the names of people being added manually to that column as people register. There will be a room associated to each name matching them as roomamates but those rooms (1a,1b, 2a, 2b) will likely be entered after the fact and not in any particular order so I want the next sheet to automatically group the names and rooms in order.

Is there a place to upload a same file to show?

Thanks in advance.