Nested Index Collect to gather data from multiple sheets

Options

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?

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Jess D ✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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!