Using Match of the top parent row to do an index search in another sheet

Mason B

Hey All!

Currently, I have a formula that if it is the Parent row (helper column shows 0) then Index multiple sheets using the SSI column. If it is a children row, use the parent SSI column to Index multiple sheets (pulling the same result). The only issue is if I have a child within a child, then the PARENT(SSI@row) no longer works as the parent is in the level 2 row, getting #NO MATCH because of that. Is there a way to format the formula below so that the children always use the top level parent to do the MATCH and not just the Parent above it?

=IF(Parent@row = 0, IFERROR(IFERROR(INDEX({Madison System Overview Dept}, MATCH(SSI@row, {Madison System Overview SSI}, 0)), INDEX({Fargo System Tracking Dept}, MATCH(SSI@row, {Fargo System Tracking SSI}, 0))), INDEX({Completed System/Periodic Review Overview Dept}, MATCH(SSI@row, {Completed System/Periodic Review Overview SSI}, 0))), IFERROR(IFERROR(INDEX({Fargo System Tracking Dept}, MATCH(PARENT(SSI@row), {Fargo System Tracking SSI}, 0)), (INDEX({Madison System Overview Dept}, MATCH(PARENT(SSI@row), {Madison System Overview SSI}, 0)))), INDEX({Completed System/Periodic Review Overview Dept}, MATCH(PARENT(SSI@row), {Completed System/Periodic Review Overview SSI}, 0))))

Paul Newcome

You can continue to use your original formula with a slight tweak.

Replace ALL instances of

PARENT(SSI@row)

with

INDEX(ANCESTORS(SSI@row), 1)

JamesB

@Mason B I think you could use the ANCESTORS formula. The top level parent of a sheet would have 0 ancestors.

Mason B

How do I leverage that in a Match rule for a different column? Bold below is where I would assume it would be utilized:

=IF(Parent@row = 0, IFERROR(IFERROR(INDEX({Madison System Overview Dept}, MATCH(SSI@row, {Madison System Overview SSI}, 0)), INDEX({Fargo System Tracking Dept}, MATCH(SSI@row, {Fargo System Tracking SSI}, 0))), INDEX({Completed System/Periodic Review Overview Dept}, MATCH(SSI@row, {Completed System/Periodic Review Overview SSI}, 0))), IFERROR(IFERROR(INDEX({Fargo System Tracking Dept}, MATCH(PARENT(SSI@row), {Fargo System Tracking SSI}, 0)), (INDEX({Madison System Overview Dept}, MATCH(PARENT(SSI@row), {Madison System Overview SSI}, 0)))), INDEX({Completed System/Periodic Review Overview Dept}, MATCH(PARENT(SSI@row), {Completed System/Periodic Review Overview SSI}, 0))))

I have tried this and getting #INCORRECT ARGUMENT SET:

=IF(Parent@row = 0, IFERROR(IFERROR(INDEX({Madison System Overview Dept}, MATCH(SSI@row, {Madison System Overview SSI}, 0)), INDEX({Fargo System Tracking Dept}, MATCH(SSI@row, {Fargo System Tracking SSI}, 0))), INDEX({Completed System/Periodic Review Overview Dept}, MATCH(SSI@row, {Completed System/Periodic Review Overview SSI}, 0))), IFERROR(IFERROR(INDEX({Fargo System Tracking Dept}, MATCH(ANCESTORS(SSI@row), {Fargo System Tracking SSI}, 0)), (INDEX({Madison System Overview Dept}, MATCH(ANCESTORS(SSI@row), {Madison System Overview SSI}, 0)))), INDEX({Completed System/Periodic Review Overview Dept}, MATCH(ANCESTORS(SSI@row), {Completed System/Periodic Review Overview SSI}, 0))))

And this gets #INVALID DATA TYPE:

=IF(Parent@row = 0, IFERROR(IFERROR(INDEX({Madison System Overview Dept}, MATCH(SSI@row, {Madison System Overview SSI}, 0)), INDEX({Fargo System Tracking Dept}, MATCH(SSI@row, {Fargo System Tracking SSI}, 0))), INDEX({Completed System/Periodic Review Overview Dept}, MATCH(SSI@row, {Completed System/Periodic Review Overview SSI}, 0))), IFERROR(IFERROR(INDEX({Fargo System Tracking Dept}, MATCH(AND(ANCESTORS(), SSI@row), {Fargo System Tracking SSI}, 0)), (INDEX({Madison System Overview Dept}, MATCH(AND(ANCESTORS(), SSI@row), {Madison System Overview SSI}, 0)))), INDEX({Completed System/Periodic Review Overview Dept}, MATCH(AND(ANCESTORS(), SSI@row), {Completed System/Periodic Review Overview SSI}, 0))))

JamesB

@Mason B

I should have read further into your formula. How about this?

MATCH(INDEX(SSI:SSI, 1, 1), {Fargo System Tracking SSI}, 0))

The index command will always pull the data from the SSI column, row 1, column 1.

Mason B

Unfortunately, that won't work as the top level parent row won't be the same row. Example below, this one is at row 81. Hoping to find something dynamic here that will leverage the rows top parent row every time to complete the MATCH

JamesB

@Mason B

At what level of indent would you consider to be the Top Level Parent row for a task?

Mason B

Level 0 - so everything level 1, 2, 3, etc under that level 0 row will use the parent row at level 0 to do the match. I do have a parent helper column that I use elsewhere if that helps:

JamesB

Mason B

Hey @JamesB so that works the same as my original formula as it only does level 0 and level 1. Is there a better formula for this helper column to always pull the level 0 row? Even for level 3, 4, etc rows?

JamesB

@Mason B My apologies, I just realized an error in my approach. By naming the column SSI2 in my formula, SS could not reference the column. it was trying to reference the SSI cell in row2.

Use this formula.

=IF(COUNT(PARENT()) = 0, SSI@row, PARENT([SSI Helper]@row))

Mason B

@JamesB hmm SmartSheet doesn't seem to like that one

JamesB

@Mason B

That is odd. Here it is in my test sheet.

Just figured it out. Your column name has a space in it. Add Brackets [SSI Helper]@row

Paul Newcome

You can continue to use your original formula with a slight tweak.

Replace ALL instances of

PARENT(SSI@row)

with

INDEX(ANCESTORS(SSI@row), 1)

Mason B

@Paul Newcome - that worked like a charm! Thank you so much!

Paul Newcome

Happy to help. 👍️