Index question

[desk.doc]

Hi all!
I am having a problem with using the index function. I am working (still) on a schedule in excel 2003. I am trying to find a way to take a name off the schedule and put it into a cell on worksheet 2 depending on the time they are scheduled. Here is the formula I am working with, but for some reason it's not reading the time, and sticking a name in that's not even associated with the time.

Please Login or Register  to view this content.

Thank you for your time and help!!

[protonLeah]

1. What is the format of the cells D6:D60, time or text?
2. What is the significance of "-C" in "9:30-C"? You put it in quotes, so it's a text string not a time value.

An experiment with cell "Dx" formatted as time (hh:mm) and using the formula below returned the correct value from "Bx":

Please Login or Register  to view this content.

[desk.doc]

Thank you for the reply. The cells d6-d60 are formatted as text and not as a date/time format. I don't have a choice in this. I have included a sample of what I am doing. On Worksheet 2 I am going to make a sheet that will take each day, Mon - Sun, and list the people working that day for what we use as a "labor chart". What I am trying to do is make the sheets search the schedule, and put the appropriate people in place for the time they are in. The schedule sheet is made, I can make the "Daily sheet", I am having a hard time getting the function to work. Thank you for your time.

[daddylonglegs]

MATCH, in this case, needs a third argument of zero, i.e.

=INDEX('FOH Schedule'!B6:B60,MATCH("9:30-C",'FOH Schedule'!D6:D60,0))

You would only omit the zero (or use 1 or TRUE) if you were looking for a "closest match" in a sorted range. See Excel help on MATCH for more.

[desk.doc]

Thank you once again Daddylonglegs. Your help in this sheet has been invaluable. The function you supplied works. I am running into a problem with a second person being scheduled at the same time. If I drag down on the formula, it lists the same person twice instead of moving down to the next person scheduled at this time. Any direction you can point me in on this issue? Thank you.