Help - Excel Database - Accommodation list

I am looking for a help.

Having a database of 20000 employees data, from the main data i want to extract data in new file according their room numbers.
Example room no.(A-1) 5 people are occupied, i want to see these 5 people in new file by typing their room number.

Vl lokup can display only one record matching the room numbers, multiple record not getting.

Any one can help, please
Justin

Try pivottable

Any other option,
i want to make a permanent file, while room occupants are changing frequently so i want to get updated report by using formula.