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?

Find more posts tagged with

Formulas

collect

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.