Index/Match with cross sheet references

Dina B

I have four sheets that I want to summarize in a separate sheet. If I am using an Index/Match formula, can I apply it to Child rows also? On my sheets, I have an amount columns, but some amount cells are sums of the two children rows beneath it. So, when I use the Index/Match formula, it only applies to the main Parent row. I am using cross sheet references. Is this possible? If so, how? Thank you in advance.

Jason Tarpinian

MATCH() function automatically finds the first match and stops searching. So if it's the parent level you need to pull in data from, you're all set with just a normal INDEX/MATCH.

If you do need to distinguish the two, I always like have a helper column with the =COUNT(ANCESTORS()) formula, which will return the hierarchy level of the row. Then you can use "Hierarchy = 0" to filter for the parent rows in SUMIFS or COUNTIFS functions.

Dina B

@Jason Tarpinian - does that work if on the summary sheet I need the individual amounts from the child rows (Hierarchy =1)? I have two different child values with their own individual values going into one batch (parent - SUM)).

Jason Tarpinian

If you need the nth child row, you might need to look into using INDEX/COLLECT, if I'm following you correctly. See my example below. In this case "Parent Grouping" and "Lookup Child #" are telling the formula which parent section to look in, and which child # to pull from that grouping. This is the formula in the "Value" column, so Parent 2, 3rd child, returns $2,000.

=INDEX(COLLECT([Return Value]:[Return Value], Parent:Parent, [Parent Grouping]@row, Hierarchy:Hierarchy, 1), [Lookup Child #]@row)