Index Match Values Based on Certain Parent Rows

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!

Screen Shot 2019-09-03 at 10.31.30 AM.png

Screen Shot 2019-09-03 at 10.55.48 AM.png

Find more posts tagged with

Construction

Formulas

Project Management

Account and User Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…