Complex Lookup Formula - need help with returning the proper values.
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), "")
Answers
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!