Vlookup of Child tasks

Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

  • MCSCOO
    Options

    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

  • MCSCOO
    Options

    Hi Paul I am still unclear on this not have any luck

  • MCSCOO
    Options

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • MCSCOO
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!