hi, first of all thanks for looking at this and helping me
hopefully :)

i'm setting up a new rota system on excel for work, but it's using
windows me and excel 2000 :(

i setup the following formula to detect what site the guard is working
at, and what hours the shift are... using the following layout

all are for rows 41-230
Column A = site names (SITELIST)
Column B = start time (
Column C = finish time (
Column D = number of hours for shift (
Column E = guard id number (M_WK1)

each guard has a personal planner.. id is D5... on a separate sheet...

the formula for finding where there id occurs is


INDEX(SITELIST,MATCH(D5,M_WK1,0),1)

Almost identical formulas for finding the start/finish/hours total for
shift.

can you tell me how i would alter that formula to find the 2nd and 3rd
instances please ?

many thanks

Craig

=INDEX(SITELIST,SMALL(IF(D5=M_WK1,ROW(M_WK1)-1),2),1)

Entered with Ctrl+shift+Enter rather than just enter since this is an array
formula should return the second instance. Change the 2 to a 3 forthe third

The -1 is used to adjust the row returned to be an offset into your list.
In my case, my list started in row 2. If you list starts in row 1, you don't
need to subtract anything. If it starts in row 10, then you would have -9
and so forth.

--
Regards,
Tom Ogilvy

" wrote:

hi, first of all thanks for looking at this and helping me
hopefully :)

i'm setting up a new rota system on excel for work, but it's using
windows me and excel 2000 :(

i setup the following formula to detect what site the guard is working
at, and what hours the shift are... using the following layout

all are for rows 41-230
Column A = site names (SITELIST)
Column B = start time (
Column C = finish time (
Column D = number of hours for shift (
Column E = guard id number (M_WK1)

each guard has a personal planner.. id is D5... on a separate sheet...

the formula for finding where there id occurs is


INDEX(SITELIST,MATCH(D5,M_WK1,0),1)

Almost identical formulas for finding the start/finish/hours total for
shift.

can you tell me how i would alter that formula to find the 2nd and 3rd
instances please ?

many thanks

Craig