Index Match Values Based on Certain Parent Rows

xwallace

First, awesome community! I've been looking all over for a solution to my problem, but cannot find anything that would translate to my situation. Hopefully I make some sort of sense describing my issue.

Brief:

I work for a construction company that specializes in high rise construction. For my department, I'm tracking progress and costs broken down by Project, Floor, and Scope of work, i.e (Project A > 1st Floor > Concrete >...) and would like to pull values specific to floor and scope.

I started with a basic Index formula, but can't figure how to make the match function work within those parameters --Maybe I need to use a different approach?

=INDEX({UID1}, MATCH(Type2, {TASK1}, 0)) * Quantity2

This brings the value that I need, but this would force me to tweak and monitor each entry due to the varying amount of Floors and Tasks we perform; I would like a formula that would be able to return the red values in the screen shot based upon matching the Type (A,B, or C) with an identified Task and/or Group.

 

I hope this makes some type of sense, been racking my brain all weekend lol.

 

Thanks! 

 

Paul Newcome

So you are trying to pull from the first screenshot based on the Group, Task, and Type in the second screenshot?

xwallace

Hey Paul, yes! That’s exactly what I’m going for!

Paul Newcome

Ok. My first suggestion is to add the word "Task" in your second sheet in the Task column. That way we can have some consistency with the data.

 

You could then add a helper column to both sheets. I'll call it "Helper" for this example.

 

In the first sheet you would use 

 

=JOIN(ANCESTORS(ID@row), ";")

 

In the second sheet, it would be 

 

=JOIN(Group@row:Type@row, ";")

.

This will give you matching data on both sheets that we can use to match against.

 

=INDEX({First Sheet UID1 Column}, MATCH(Helper@row, {First Sheet Helper Column}, 0)) * Quantity@row