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

  • I'm having a similar issue trying to match to 5 sheets to bring in one piece of data. The formula works except that it brings in #No Match. I've tried adding ," ") at the end but that is not working. Formula is:

    =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)))

    Thank you for anyone that can help.

  • 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!

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Automatic sorting, sorting with filters, saving sort settings
    • Spell checking
    • Report PDF generator that supports grouped and summarized reports

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!