Lookup values for items under specific parents
I'm trying to cross-reference data on another sheet that has identical task names under different parent tasks like in the below example.
I'm using a vlookup formula currently that works for unique task names, but obviously not for these. Any thoughts?
James
Answers
-
There's a few different ways you could do this.
One way to do this would be to build out separate INDEX(MATCH formulas (which would work the same as a VLOOKUP), but the range to match in each of the MATCH portions of your formula would be per-selection of child rows.
For example:
=INDEX({Column with Value to Return}, MATCH([Value to match]@row, {Pipe Stress Children Range}, 0))
Then:
=INDEX({Column with Value to Return}, MATCH([Value to match]@row, {Structural Steel Children Range}, 0))
Another idea would be to add in a "helper column" into this source sheet which would create a unique task name (ex. that would add the Parent Task name to the row with the child task name so that it's identifiable). Then you would need to have this same unique name somewhere in your destination sheet so that the formula can find a match. Does that make sense?
Let me know if either of these ideas will work for you or if you have any more questions and I'd be happy to explain further.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!