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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!