Lookup a Value and Return Multiple Results

Hi all,

Help please !!

I have been using this formula to do lookup and return values
"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"

I have come across a little problem and needing a little help on a solution.

i'm using this formula in a roster scene to pick up people that call in sick and display their rostered job in a different cell.
the problem that i am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another,
then employee 2 calls in sick for the 1pm shift (the call was made after i have already rostered employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
is there a way to stop it from changing the value once a value is entered in a corresponding cell?

I would like to thank Member "tigeravatar" for providing the formula totarial in the first place below is the link to the original formula thread.
http://www.excelforum.com/tips-and-t...e-results.html

Hi Tony, Thank you for looking at my issue.

Below is a little extract of the file.
As you can seen I have already placed 2 S/L in the left section of the file. The formula has transferred the required data to the right hand side.
In the file if I assign a name to cover job "342" then afterwards job "309" calls in sick. On the right hand side under "sick leave duties to cover" 309 is automatically placed on top of 342 and 113. So the assigned name to cover 342 is now pointing at 309.

I'm just looking at a way to stop that if possible.

As you can see the duty number in the sick leave column are in order from the spreadsheet. So if a duty calls in sick from above (earlier start time, as it is sorted by that), the duty on the right hand side is placed above the previously placed duty.
I don't know if I am explaining it right. But if you type "s/l" in the "code" section and above the ones that are already marked, u'll see that the duty you type in will be placed above that in tje right hand side " sick leave section".

Maybe there is a code I can put in to check the "j" column and if it has any value in it then wont change vale if an earlier duty calls in sick.

thank you for your help.

See how duty 308 is on top of 342 and 113. Had I already assigned a driver to cover 342 in column j row 26 before 308 called in sick. It will now show the assigned name in j26 to be covering 308 instead of 342.

Thank you for your help Tony.