Index & Match

Originally Posted by samprince

Hi all,

I have traditionally used the following forumula:
=INDEX($E$1:$AZ$1,MATCH(MAX(E3:AZ3),E3:AZ3,0))

to match a value from row 3 with the corresponding header in Row 1.

I have made a quick table that shows up times staff are supposed to work on a specific task.

example:
in Row E1 to AZ1 are the times of the day ~ 00:00 to 23:30
in
A3 SamPrince & E3 to AZ3 cells in which I can put a 1 in if I am working then.

These are then conditionally formatted to go Orange if I put a 1 in.

in AR1:AU1 I have entered 1's

as such to showing working from 19:30 to 21:30

in C3 I wish to match the start time I.e the furst colum left with a 1 in it, & in D3 match the end time, ie the colum furthest right with a 1 in it.

Any ideas?
Ty
SP

Hi,

=OFFSET($F$1,0,MATCH(1,F2:AZ2,0)-1)

will find the first

and

=OFFSET($F$1,0,MATCH(1,F2:AZ2,0)-1+COUNTIF(F2:AZ2,1))

the second

hth
---