List cells that meet condition

Hi I need some help with the following:

I have two columns of data,

E7350:E8080 - Room Ref Range
S7350:S8080 - No Access Range

I need a formula that when I drag it down will list all of the cells in column E where the correpsonding cell in column S equals any of the following... 'Keyholder Not Present', 'In Use', 'Blocked Access', 'Other'


Any help greatly appreciated.

Jason

Example data:


-----E----------S----
1---One-------Keyholder Not Present
2---Two
3---Three------In Use
4---Four-------Other
5---Five-------None
6---Six
7---Seven-----In Use

Expected Results:

One
Three
Four
Seven

Hi MarvinP, thanks for reply. I am familiar with auto filtering but it won't work for this purpose.

The workbook I'm creating at the moment will be a report template that will reference a sheet that does not yet exist and will be moved in at a later date. Therefore I need a formula to do this so I can add in the indirect function.

I know it's possible as I've seen it done before, just not sure how.