Complex Lookup Formula - need help with returning the proper values.

Jed Rusyniak

I'm working with a sheet that is being uploaded daily with new information via Datashuttle. The sheet is populated with the [Route ID], [Warehouse Code] and then in turn i'm trying to auto populate the [Assigned Planner]. The warehouse code has both the warehouse of pick up (which is where the planner physically sits, so it is easy for me to know which planner is assigned), but it also will contain the warehouse where the load will be shipped to. In the screenshot below, what I would need to have happen is for the formula to see that [Route ID] 7 has a pick up from INDY3 and INDY4 and therefore all rows within [Route ID] 7 would have both Diamond and Christa as the [Assigned Planner]. In contrast Route 9, 10, & 11 would only have Sarahi as the planner as it is only picking up from one of the INDY locations.

I'm not sure what formula I might need that would accomplish this. Any help would be appreciated. My goal is that each row of data for each Route ID has the assigned planners populated.

We have four buildings that are pick up locations (INDY1, INDY2, INDY3, & INDY4) - I have a dedicated planner for each one of these and that is all referenced in another helper sheet. The ship to locations that will also show in the Warehouse Code column will change and can not be used in a logical statement.

=IFERROR(INDEX(COLLECT({Contact}, {facility}, [Warehouse Code]@row), 1), "")

Paul Newcome

You would use a JOIN/COLLECT, but that would only output a text string. There is currently no way to have a formula populate multiple usable contacts within a single cell.