Vlookup of Child tasks

MCSCOO

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.

Paul Newcome

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

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

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

MCSCOO

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.

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

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

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)