Expanding My Index to additional Sheets

Augusto Martinez

I recently created a new archive file and want to update the formula so that it will also search the new sheet. Here is the current formula I have. it works on current sheets but not the new archive. Any help or suggestions would be appreciated.

=UPPER(IFERROR(IF(LEFT([Excel PO Approval Number REQUIRED]@row , 2) = "PO", INDEX({ECS PO Log Range 6}, MATCH([Excel PO Approval Number REQUIRED]@row , {ECS PO Log Range 8}, 0)), INDEX({ECS Capital Commitments Log ARCHIVE Status}, MATCH([Excel PO Approval Number REQUIRED]@row , {ECS Capital Commitments Log ARCHIVE Range 7}, 0))), INDEX({ECS Capital Commitments Log Approval Status}, MATCH([Excel PO Approval Number REQUIRED]@row , {ECS Capital Commitments Log Commitment #}, 0), INDEX({ECS PO Log Archive - From 09/01/2025 Range 1}, MATCH([Excel PO Approval Number REQUIRED]@row , {ECS PO Log Archive - From 09/01/2025 Range 2}, 0)))))

jmyzk_cloudsmart_jp

Hi @Augusto Martinez

When adding a new archive sheet, Smartsheet won’t automatically update your formulas — you need to create new cross-sheet references for the new sheet before the formula can use it.

There are two ways to show the lookup results:

Case 1: Show old and new archive results in separate columns

If you want:

• Old archive lookup → Column A
• New archive lookup → Column B

Then yes, you need a separate column, and each column needs its own cross-sheet references.

Case 2: Combine both archive results into one column

If the intent is:

• Combine both archives → Column A

Then Column A’s formula must reference both the old and new archive ranges.

You can also stack both results in the same cell using a line break:

=OldArchiveLookup+ CHAR(10)+ NewArchiveLookup

This shows the old sheet result on the first line and the new sheet result on the second line.

The formula should look like this;

=IFERROR(

    UPPER(

        INDEX(

            {ECS Capital Commitments Log ARCHIVE Status},

            MATCH(

                [Excel PO Approval Number REQUIRED]@row,

                {ECS Capital Commitments Log ARCHIVE Range 7},

                0

            )

        )

    ),

    ""

)

+

IFERROR(
CHAR(10) +

    UPPER(

        INDEX(

            {ECS Capital Commitments Log ARCHIVE 2025 Status},

            MATCH(

                [Excel PO Approval Number REQUIRED]@row,

                {ECS Capital Commitments Log ARCHIVE 2025 Range 7},

                0

            )

        )

    ),

    ""

)

Please note that the second INDEX(MATCH()) should use separate references to the new sheet.

If you archive regularly, consider placing your active sheet and archive sheet in a folder, then copying that folder when creating a new archive. This keeps all formulas and cross-sheet references intact, so you don’t have to rebuild them each time.

Hope this helps!