Returning Multiple Values from a list

[w0lv3n]

Hey all,

I have a list of employees as part of a leave calendar.
The employees are listed left ot right on the top row, dates listed top to bottom on the left most column. If an employee is on leave the corresponding cell will contain "RnR".

I want to be able to make a simple report sheet, where the date is entered and the names are returned for who is on RnR for that date. I have used VLOOKUP with similar sheets, but this time I want to return a list of employees rather than a single value.

Im not sure how to do this, it may involve arrays, which I have not begun to use yet?

Any help very much appreciated!

-w0lv3n

[davidh182]

The problem here is that the output is of unknown length.

My approach would be:
1. Use Match to identify the correct row.
2. Use Offset to create a row which contains the column number if it is RnR, otherwise 9999
3. Use Small to sort the column numbers
4. Use Offset to regain the employee names.

The intermediate lines can be hidden on other pages.

With correct referencing all the formulas can be dragged out.
Here it is done:

http://f-eeg.com/images/RnR.ods

[w0lv3n]

Thanks mate, ill see about implementing that into my sheets.
Appreciate the reply!