Microsoft Excel Floor Plan Data Connection

We are creating an Excel booking-type sheet for local events, and would like to connect names with the rooms of a floorpan we are developing. I want to be able to add a name, connect the room number, and for that name (and any others with that room number) to show up in that room. I want them to wrap-text (rather than showing up as a single sentence), and to only allow a certain number of entries per "room". I've attached the file we would like to work with. The first column is the attendee's last name, the second is the first name, as we must sort them by last name. The third column is the full name which is what will be imputed into the room grid, and the fourth column is their corresponding room assignment. What I would like to be able to do is to type the room assignment in the fourth column, then to have the corresponding name in the third cell populate in that specific room. It seems that this should be just a quick formula solution, so what will it take to make this happen?