vlookup with employees with the same shift

[jordiejones]

So I have been working on a sales floor planner that pulls a ton of info onto one sheet that would be printed out every day. Part of this sheet is the schedule for the day. I have a dummy workbook showing how I am pulling data but I have a vlookup problem where if more than one person is working the same shift, it only looks up the first record it finds. Its kind of a big staff and on a rare occasion 11 people could theoretically come in at the same time.

I'm Excel self taught and if there is a better more efficient way to get to the same result, by all means!!!!

What I want:

Column A Column B
Mark 10-6
Angie 10-3
Bill 12-8
Larry 12-8
Lenny 12-5
Micky 3-8

What Im getting:

Column A Column B
Mark 10-6
Angie 10-3
Bill 12-8
Bill 12-8
Lenny 12-5
Micky 3-8


here is a RapidShare link to the file
Example of Pull from Schedule

 

[jordiejones]

I've tried sorting it how I was before and using =IF(I12="","",INDEX($A$12:$A$22,MATCH(I12,$B$12:$B$22,0))) but Im still returning duplicates which was the same problem I was having before

-Jordan

 

[T. Valko]

I've tried sorting it how I was before and using =IF(I12="","",INDEX($A$12:$A$22,MATCH(I12,$B$12:$B$22,0))) but Im still returning duplicates which was the same problem I was having before

-Jordan

Ok, I made some changes.

In your sample file...

• Changed the "sort order" formula
• Changed the "rank" formula
• Deleted a few other columns of helper formulas
• Deleted the named ranges I created in schedule(1).xlsx
• Changed the formulas that generate the sorted list

Here's the new version of your file.

schedule(2).xlsx 16kb

http://cjoint.com/?1dtpgRkiEZM

 

[jordiejones]

thank you so much. If its any consolation, Ive learned a freaking ton. Before your help I didn't know about index, row, match, naming ranges with formulas, and seeing the logic behind it has made me understand "what am I trying to get my data to do?" I hope that some day I will be able to provide the same assistance to another troubled excel novice.

Jordan