Index Match function for children rows on two sheets

psingh

Hi there,

I am trying to index match on data in the second level children rows from Sheet 1 to second level children rows on Sheet 2, and am getting this error messages. Screenshots are at the end.

Screenshots:

Sheet 1 (data reference sheet)

Sheet 2 (pulling data from source source sheet into this sheet)

If I try to use the usual formula in Sheet 2 I get: "#NO MATCH"

=INDEX({Sheet 1 Start Date}, MATCH(ID@row, {Sheet 1 ID}, 0))

If I try to use "CHILDREN" in the formula in Sheet 2 I get: "#UNSUPPORTED CROSS-SHEET FORMULA"

=INDEX(CHILDREN({Sheet 1 Start Date}), MATCH(CHILDREN(ID@row), CHILDREN({SHEET 1 ID ID}), 0))

Is there a way to make this work? I saw some discussion columns, but I can't seem to find a solution.

Thank you!

jmyzk_cloudsmart_jp

Hi @psingh

Your first formula has no problem. Your reference may have a problem.

https://app.smartsheet.com/b/publish?EQBCT=627163b327084456a0f28b44dbb8088d

Sheet 1

https://app.smartsheet.com/b/publish?EQBCT=e32e9f3300ff4a0a83bd3320359bcbb5