How do I allow for multiple values in VLOOKUP? in Excel Worksheet Functions    |   |  Help   Search For:     In:   Office   Excel     Excel General Questions     Excel New users     Excel Application Errors     Excel Charts     Excel Programming     Excel Setup     Excel Worksheet Functions              Indy_Ball 5/1/2006 10:23 AM PST   Question   I have a list of people that are working particular shifts which are set up in four to five hour increments. Some people are working consecutive shifts (working eight/nine hours in a day). I am trying to show each person's schedule with beginning and end times using VLOOKUP. It is working just fine if the people only work one shift per day. The multiple shift people only have their first shifts display. I would like to LOOKUP based upon the person's name giving the first shift's start time and the second shift's end time and haven't found a good combination of functions to use. Any suggestions?     Was this post helpful to you?         Reply |   Top           Biff 5/1/2006 11:43 AM PST   Answer   Hi! Do the shifts span past midnight like 10:00 PM - 6:00 AM ? If not then you could use something like this: For the earliest start time: =MIN(IF(A1:A10="some_name",B1:B10)) For the latest end time: =MAX(IF(A1:A10="some_name",B1:B10)) You can replace "some_name" with a cell reference. These are array formulas and must be entered using the key combination of CTRL,SHIFT,ENTER. Biff "Indy_Ball" <Indy_Ball@discussions.microsoft.com> wrote in message news:E5802B1C-0E1A-4204-8B9A-5F196BB92982@microsoft.com... >I have a list of people that are working particular shifts which are set up > in four to five hour increments. Some people are working consecutive > shifts > (working eight/nine hours in a day). I am trying to show each person's > schedule with beginning and end times using VLOOKUP. It is working just > fine > if the people only work one shift per day. The multiple shift people only > have their first shifts display. I would like to LOOKUP based upon the > person's name giving the first shift's start time and the second shift's > end > time and haven't found a good combination of functions to use. Any > suggestions?     Was this post helpful to you?         Reply |   Top           Indy_Ball 5/1/2006 12:26 PM PST   Biff - That worked marvelously. Thanks "Biff" wrote: > Hi! > > Do the shifts span past midnight like 10:00 PM - 6:00 AM ? > > If not then you could use something like this: > > For the earliest start time: > > =MIN(IF(A1:A10="some_name",B1:B10)) > > For the latest end time: > > =MAX(IF(A1:A10="some_name",B1:B10)) > > You can replace "some_name" with a cell reference. > > These are array formulas and must be entered using the key combination of > CTRL,SHIFT,ENTER. > > Biff > > "Indy_Ball" <Indy_Ball@discussions.microsoft.com> wrote in message > news:E5802B1C-0E1A-4204-8B9A-5F196BB92982@microsoft.com... > >I have a list of people that are working particular shifts which are set up > > in four to five hour increments. Some people are working consecutive > > shifts > > (working eight/nine hours in a day). I am trying to show each person's > > schedule with beginning and end times using VLOOKUP. It is working just > > fine > > if the people only work one shift per day. The multiple shift people only > > have their first shifts display. I would like to LOOKUP based upon the > > person's name giving the first shift's start time and the second shift's > > end > > time and haven't found a good combination of functions to use. Any > > suggestions? > > >     Was this post helpful to you?         Reply |   Top           Biff 5/1/2006 2:00 PM PST   You're welcome. Thanks for the feedback! Biff "Indy_Ball" <IndyBall@discussions.microsoft.com> wrote in message news:D7B69716-BB34-45C9-BA1D-6B069BC9AF63@microsoft.com... > Biff - > That worked marvelously. > Thanks > > "Biff" wrote: > >> Hi! >> >> Do the shifts span past midnight like 10:00 PM - 6:00 AM ? >> >> If not then you could use something like this: >> >> For the earliest start time: >> >> =MIN(IF(A1:A10="some_name",B1:B10)) >> >> For the latest end time: >> >> =MAX(IF(A1:A10="some_name",B1:B10)) >> >> You can replace "some_name" with a cell reference. >> >> These are array formulas and must be entered using the key combination of >> CTRL,SHIFT,ENTER. >> >> Biff >> >> "Indy_Ball" <Indy_Ball@discussions.microsoft.com> wrote in message >> news:E5802B1C-0E1A-4204-8B9A-5F196BB92982@microsoft.com... >> >I have a list of people that are working particular shifts which are set >> >up >> > in four to five hour increments. Some people are working consecutive >> > shifts >> > (working eight/nine hours in a day). I am trying to show each person's >> > schedule with beginning and end times using VLOOKUP. It is working >> > just >> > fine >> > if the people only work one shift per day. The multiple shift people >> > only >> > have their first shifts display. I would like to LOOKUP based upon the >> > person's name giving the first shift's start time and the second >> > shift's >> > end >> > time and haven't found a good combination of functions to use. Any >> > suggestions? >> >> >>     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies