Help with Collect / Match / Vlookup formula

Jed Rusyniak

Working on a collection of sheets that I use between the different staffing agencies that service my operation.  My Sr. Ops manager publishes a sheet (with the green shaded cells in the attachments below) with the labor order (by agency) each day for up to a week at a time.  I am wanting to have the agency specific sheet (blue shaded cells in the attachments below) reference this master order sheet and look at the date and shift (am/pm) to return the ordered value by position code.  Initially thought maybe a vlookup, but that doesn't allow me to pull in different values for the same day depending on AM or PM shift.  I think a Collect formula is the way to go, just haven't been able to find a way to write this. 

Paul Newcome

Try using a JOIN/COLLECT instead. If you are able to specify criteria so that only one row would be pulled, it would produce the desired results.

 

Something along the lines of...

 

=JOIN(COLLECT({Associates Ordered}, {Shift Date}, [Shift Date]@row, {Shift}, Shift@row, {Agency}, "Lyneer"))