Indexing from Multiple Sheets

Question

Good afternoon Smartsheet Fam,

I'm trying to create an index formula across 4 sheets to pull into a single sheet.  The formula I have tried is:

=IFERROR(INDEX({AHM.Client}, MATCH([CU Project ID]@row, {AHMPlan.CUProjectID}, 0)), INDEX({ahmne.client}, MATCH([CU Project ID]@row, {ahmne.cuprojid}, 0)), INDEX({ahmse.client}, MATCH([CU Project ID]@row, {ahmse.cuprojid}, 0), INDEX({ahmw.client}, MATCH([CU Project ID]@row), {ahmwe.cuprojid}, 0)))

It seems to have worked for the first two sets of criteria, but isn't working once I had a third and forth set of criteria.

Thoughts?

jmyzk_cloudsmart_jp · Answer

Hi @tracy.heppes40761

Good question — you were very close, but the issue stems from incorrectly mixing two different patterns (MATCH vs COLLECT).

Your original formula (readable format)

=IFERROR(

    INDEX(

        COLLECT(

            {ahm.start},

            {ahm.task}, "Enrollment Received/Approved",

            {AHMPlan.CUProjectID}, MATCH([CU Project ID]@row), 0

        )

    ),

    IFERROR(

        INDEX(

            COLLECT(

                {ahmne.start},

                {ahmne.task}, "Enrollment Received/Approved",

                {ahmne.cuprojid}, MATCH([CU Project ID]@row), 0

            )

        ),

        IFERROR(

            INDEX(

                COLLECT(

                    {ahmse.start},

                    {ahmse.task}, "Enrollment Received/Approved",

                    {ahmse.cuprojid}, MATCH([CU Project ID]@row), 0

                )

            ),

            IFERROR(

                INDEX(

                    COLLECT(

                        {ahmw.start},

                        {ahmw.task}, "Enrollment Received/Approved",

                        {ahmw.cuphase}, MATCH([CU Project ID]@row), 0

                    )

                ),

                ""

            )

        )

    )

)

What was wrong with the formula

1. MATCH used inside COLLECT (invalid usage)

You wrote:

{AHMPlan.CUProjectID}, MATCH([CU Project ID]@row), 0

This is incorrect because:

* MATCH() is not used inside COLLECT
* range, criteria

Correct pattern:

{CUProjectID range}, [CU Project ID]@row

2. INDEX(COLLECT(...)) missing row index

INDEX() requires a position:

INDEX(…, 1)

Without , 1, it will error

3. Mixed logic: INDEX/MATCH vs COLLECT

You combined:

* INDEX + MATCH logic 
* COLLECT logic

These are different approaches and shouldn’t be mixed

4. Inconsistent column references

Example:

{ahmw.cuphase}

Should match the same key column:

{ahmw.cuprojectid}

Correct formula

=IFERROR(

    INDEX(

        COLLECT(

            {AHMPlan.start},

            {AHMPlan.task}, "Enrollment Received/Approved",

            {AHMPlan.cuprojectid}, [CU Project ID]@row

        ),

        1

    ),

    IFERROR(

        INDEX(

            COLLECT(

                {ahmne.start},

                {ahmne.task}, "Enrollment Received/Approved",

                {ahmne.cuprojectid}, [CU Project ID]@row

            ),

            1

        ),

        IFERROR(

            INDEX(

                COLLECT(

                    {ahmse.start},

                    {ahmse.task}, "Enrollment Received/Approved",

                    {ahmse.cuprojectid}, [CU Project ID]@row

                ),

                1

            ),

            IFERROR(

                INDEX(

                    COLLECT(

                        {ahmw.start},

                        {ahmw.task}, "Enrollment Received/Approved",

                        {ahmw.cuprojectid}, [CU Project ID]@row

                    ),

                    1

                ),

                ""

            )

        )

    )

)

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

Updated source sheets

Additional Information on Sheet Reference

You can use Sheet Reference Manager to duplicate and edit the existing references to create additional references for the start and task.

Duplicate

Edit reference and name

Added references

tracy.heppes40761 · Answer

I tried to apply both of these sets of logic to a new indexing formula and also came up with an error:

=IFERROR(INDEX(COLLECT({ahm.start}, {ahm.task}, "Enrollment Received/Approved", {AHMPlan.CUProjectID}, MATCH([CU Project ID]@row), 0)), IFERROR(INDEX(COLLECT({ahmne.start}, {ahmne.task}, "Enrollment Received/Approved", {ahmne.cuprojid}, MATCH([CU Project ID]@row), 0)), IFERROR(INDEX(COLLECT({ahmse.start}, {ahmse.task}, "Enrollment Received/Approved", {ahmse.cuprojid}, MATCH([CU Project ID]@row), 0)), IFERROR(INDEX(COLLECT({ahmw.start}, {ahmw.task}, "Enrollment Received/Approved", {ahmw.cuphase}, MATCH([CU Project ID]@row), 0)), ""))))

I am trying to collect a dates across multiple sheets where the task and cu project ID match.  The following formula works for one sheet:

=IFERROR(INDEX(COLLECT({ahm.start}, {ahm.task}, "Enrollment Received/Approved", {AHMPlan.CUProjectID}, [CU Project ID]@row), 1), "")

I'm having issues replicating and nesting.  Any suggestions are greatly appreciated!

tracy.heppes40761 · Answer

Thank you. The Join Collect function doesn't work for this because now it has as many times as the client name is listed on the sheet i'm referencing.  However Option1 seems to have worked perfectly.  Thank you so much!