Trouble Matching Across Multiple Sheets – Formula Brings Back #No Match

This discussion was created from comments split from: INDEX/Match, to not display "#NO MATCH".

Answers

  • SSFeatures
    SSFeatures ✭✭✭✭✭

    Hi @JenniferN,

    I formatted your formula to make it easier to read:

    =IFERROR(
        IFERROR(
            IFERROR(
                IFERROR(
                    INDEX({Sheet 1 Range 1}, MATCH(SKU@row, {Sheet 1 Range 2}, 0)),
                    INDEX({Sheet 2 Range 3}, MATCH(SKU@row, {Sheet 2 Range 7}, 0))
                ),
                INDEX({Sheet 3 Range 5}, MATCH(SKU@row, {Sheet 3 Range 2}, 0))
            ),
            INDEX({Sheet 4 Range 1}, MATCH(SKU@row, {Sheet 4 Range 2}, 0))
        ),
        INDEX({Sheet 5 Range 1}, MATCH(SKU@row, {Sheet 5 Range 2}, 0))
    )
    

    One of your IFERROR functions has 2 INDEX functions inside of it. And you have 5 INDEX functions but only 4 IFERROR functions. You probably meant to add another IFERROR function for one of the 2 indexes that I made bold.

    Since this formula is pretty large, I recommend editing it in a text editor rather than in SmartSheet, and then copy and paste it into SmartSheet. If you edit in a text editor then you can format the formula using multiple lines, and it's easier to modify.

    Hope this helps!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds essential features into Smartsheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!