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

System

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

JenniferN

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

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!