Nested Index Collect to gather data from multiple sheets

Jess D

Hi all,

I am trying to create a sheet that pulls data from multiple others. I want to populate the person from Sheet 1 when the collection sheet column for client matches the Sheet 1 column for client.

The basic formula from one sheet works:

=IFERROR(INDEX(COLLECT({Sheet1 Person}, {Sheet1 Client}, Client@row), 1,), "Missing Person")

I've tried:

Option 1:

=IFERROR(INDEX(COLLECT({Sheet1 Person}, {Sheet1 Client}, Client@row), 1,), (COLLECT({Sheet2 Person}, {Sheet2 Client}, Client@row), 1,),"Missing Person")

Option 2:

=IFERROR(INDEX(COLLECT({Sheet1 Person}, {Sheet1 Client}, Client@row), 1,), OR(COLLECT({Sheet2 Person}, {Sheet2 Client}, Client@row), 1)),"Missing Person")

I've tried other variants with various additions/subtractions of paranthesis and the INDEX.

How should it look when nesting additional sheets?

Kelly Moore

Hey @Jess D

Try this

=IFERROR(IFERROR(INDEX(COLLECT({Sheet1 Person}, {Sheet1 Client}, Client@row),1), INDEX(COLLECT({Sheet2 Person}, {Sheet2 Client}, Client@row),1)),"Missing Person")

Will this work for you?
Kelly

Kelly Moore

Hey @Jess D

Try this

=IFERROR(IFERROR(INDEX(COLLECT({Sheet1 Person}, {Sheet1 Client}, Client@row),1), INDEX(COLLECT({Sheet2 Person}, {Sheet2 Client}, Client@row),1)),"Missing Person")

Will this work for you?
Kelly

Jess D

That worked! Thank you. Now I need to expand it to a total of from 4 sheets. lol But this template should help me to do so.

Kelly Moore

Hey @Jess D

To expand it, add IFERRORs within the formula, keeping the final IFERROR at the very beginning. In other words, add an IFERROR to your Sheet 2 and your Sheet 3 Index/Collect. Do them one at a time so you keep your parentheses straight each time.

shout out if you need help

Kelly