Lookup values for items under specific parents

James Chestnut

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

Genevieve P.

Hi @James Chestnut

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