Vlookup of Child tasks
Hello,
I have a sheet with Unique parent tasks. The Child task however or not unique and repeat within each different Parent Task.
Example
(Parent Task) Job 10
Child Task 1 Paid
Child Task 2 not Paid
(Parent Task) 11
Child Task 1 Paid
Child Task 2 not Paid
What I need to do is retrieve data from a column in another sheet that has the same parent and child tasks from the original Sheet.
Answers
-
You would need to have a column on the lookup sheet that also contains the parent row data on each of the child rows. Then you can use an INDEX/COLLECT to be able to reference both columns.
-
Hi Paul,
Thank you, yes the Column on the lookup sheet contains the same data.
I’ll give this a shot to see how close I can get and reply here either way,
John
-
Hi Paul I am still unclear on this not have any luck
-
Here are the two sheets. In the Main sheet I want ot pull over some of the column data related to the permits like the status and the start and complete dates. I would like the information to automatically update in the Main sheet when it is update in the Source sheet.
Thanks for your help
-
Hi @MCSCOO
As long as the value you're searching for is unique (so, "City_PS" doesn't show up anywhere else), you should be able to write an INDEX(MATCH statement as Paul suggested.
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))
So in your case, something like:
=INDEX({Status Column}, MATCH([Task Name]@row, {Task Name Column}, 0))
Let us know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
Thank you I am close. However, The "City_PS" does repeat. However it is a child task. The Parent Tasks Are unique and do not repeat. Being that the Parent Task are unique can I isolate the Parent task and then grab the Status from the Child task "City_PS"?
I have had success with the first part of your formula. I think I am just missing this last piece thank
you!
John
-
You need a helper column on the source sheet that contains the parent row data.
=PARENT([Task Name]@row)
Then you would use an INDEX/COLLECT similar to this...
=INDEX(COLLECT({Status Column}, {Helper Column}, @cell = "Submitted", {Task Column}, @cell = "City_PS"), 1)
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!