Returning Multiple Information Sets through VLookup or Index Match for a Schedule?

[cppqueen]

I am writing a scheduling program for a complicated schedule. I have a schedule Matrix for each day of the week with 6 columns: Staff; Client #; Special (for extra shift information); Time In; Time Out; and Shift Hours. This is vertically organized primarily by client number, but not chronologically.

I am trying to pull information from the first 3 columns to populate a schedule matrix that is organized by staff name so staff can easily find their different shifts for the week. I have been using VLookup to pull this information and it works pretty well unless I have someone scheduled for two different shifts. Then, I can only get the information for the first time a staff's name appears vertically. I have been entering the information manually in the Specials column, but I find this creates a lot of problems if I make changes and forget to update the Specials text or forget to add the second shift information into the Specials text. I am attempting to work with Index Match but the Match part requires me to identify a specific value to match and the information in the Specials column varies too widely to set up matches for each possibility.

I want to find a way to combine two sets of shift information if I enter a staff's name twice without having to change the case of the letters in their name. Please help!

 

[C Moore]

Would a PivotTable not do this?

 

[cppqueen]

Would a PivotTable not do this?

I did play around with this a little bit, however, I find it isn't as clean as the format that I have now. I would like staff to be able to look horizontally across the days of the week and find their shifts clearly. But with the pivot tables, if they don't have a shift on a given day, it doesn't show up in the pivot table. I could fix this by adding rows at the bottom of the weekly scheduling matrix for staff who have the day off, but this adds work for me. Also, if say they have two shifts on a given day, it pushes the next staff members name down. These are pretty complicated schedules and I want my staff to be able to read it easily.

Is there not a way to have two vlookup functions in a formula return two sets of information into the same cell?

thank you

 

[C Moore]

If you know both formulas, you can joim them with something like =formula1 & ", " & formula2

 

[cppqueen]

except the vlookup for the 2nd formula will still only read the first occurrence of the name.

 

[C Moore]

I haven't checked this, but I think if there are only two shifts you can use index-match. A match function with no match type (match value,match array, match type) and leave match type blank, searches from the bottom of a column, and an exact match (use 0 for match type) starts from the top. In theory, if there are two shifts each formula should find a different one. You'd need to setup a check for two shifts so it wouldn't repeat the one shift twice.

 

[cppqueen]

thank you, I will play with this and see what I come up with.