# Nested Index Collect to gather data from multiple sheets

✭✭

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?

Tags:

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭

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.

• ✭✭✭✭✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!