Nesting INDEX/MATCH

Here's a pretty version of the formula I'm trying to implement:

I can nest two INDEX/MATCHs and it works fine:

=IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0))), "")

But if I try to do three or more, I get #INCORRECT ARGUMENT SET:

=IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0)), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0))), "")

or ultimately:

=IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0)), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0)), INDEX({FL Region Review Range 1}, MATCH([RE Store # Lookup]@row, {FL Region Review Range 2}, 0)), INDEX({Great Lakes Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review Range 2}, 0)), INDEX({HI Region Review Range 1}, MATCH([RE Store # Lookup]@row, {HI Region Review Range 2}, 0)), INDEX({NE and NYC Review Range 1}, MATCH([RE Store # Lookup]@row, {NE and NYC Review Range 2}, 0)), INDEX({NW Region Review Range 1}, MATCH([RE Store # Lookup]@row, {NW Region Review Range 2}, 0)), INDEX({South Region Review Range 1}, MATCH([RE Store # Lookup]@row, {South Region Review Range 2}, 0)), INDEX({Southern OH Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Southern OH Region Review Range 2}, 0)), INDEX({TX Region Review Range 1}, MATCH([RE Store # Lookup]@row, {TX Region Review Range 2}, 0))), "")

Is there a limit or is this a syntax issue?

Thank you, Robert

Best Answer

  • Robert Francher
    Robert Francher ✭✭✭✭
    Answer ✓

    I co-worker helped me figure this out. Here's the final formula:

    =IFERROR(INDEX({CA Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {CA Region Review - Store #}, 0)), 

    IFERROR(INDEX({Carolinas Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review - Store #}, 0)), 

    IFERROR(INDEX({Central Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Central Region Review - Store #}, 0)), 

    IFERROR(INDEX({FL Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {FL Region Review - Store #}, 0)), 

    IFERROR(INDEX({Great Lakes Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review - Store #}, 0)), 

    IFERROR(INDEX({HI Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {HI Region Review - Store #}, 0)), 

    IFERROR(INDEX({NE and NYC Review - Store Exit Date}, MATCH([RE Store # Lookup]@row, {NE and NYC Review - Store #}, 0)), 

    IFERROR(INDEX({NW Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {NW Region Review - Store #}, 0)), 

    IFERROR(INDEX({South Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {South Region Review - Store #}, 0)), 

    IFERROR(INDEX({Southern OH Region - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Southern OH Region - Store #}, 0)), 

    IFERROR(INDEX({TX Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {TX Region Review - Store #}, 0)), "")))))))))))

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Robert Francher

    The IFERROR function works like this:

    (first do this, if you have an error doing this then do that instead).

    I've counted 11 INDEX/MATCH function. So you're missing a bunch of IFERROR statements in your formula. 8 I think.

    So you have to rework your formula adding all those IFERROR function to what you want it to do.

    Hope it helped!

  • Robert Francher
    Robert Francher ✭✭✭✭

    I tried the following but got the same result:

    =IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0)), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0)), INDEX({FL Region Review Range 1}, MATCH([RE Store # Lookup]@row, {FL Region Review Range 2}, 0)), INDEX({Great Lakes Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review Range 2}, 0)), INDEX({HI Region Review Range 1}, MATCH([RE Store # Lookup]@row, {HI Region Review Range 2}, 0)), INDEX({NE and NYC Review Range 1}, MATCH([RE Store # Lookup]@row, {NE and NYC Review Range 2}, 0)), INDEX({NW Region Review Range 1}, MATCH([RE Store # Lookup]@row, {NW Region Review Range 2}, 0)), INDEX({South Region Review Range 1}, MATCH([RE Store # Lookup]@row, {South Region Review Range 2}, 0)), INDEX({Southern OH Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Southern OH Region Review Range 2}, 0)), INDEX({TX Region Review Range 1}, MATCH([RE Store # Lookup]@row, {TX Region Review Range 2}, 0))))))))))), "")

    I appreciate the feedback. I'll keep working on it.

  • Robert Francher
    Robert Francher ✭✭✭✭

    I'm getting #INCORRECT ARGUMENT SET using to search 3 or more:

    =IFERROR(IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0)), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0)))), "")

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    @Robert Francher

    #INCORRECT ARGUMENT SET means your formula isn't correctly written.

    =IFERROR(IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0))),INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0)))

    Since you're nesting IFERRORS on the first argument, your parenthesis cannot be stacked at the end of the formula. You have to close them after each INDEX/MATCH argument.

    =IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), ""),INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0))), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0))), INDEX({FL Region Review Range 1}, MATCH([RE Store # Lookup]@row, {FL Region Review Range 2}, 0))), INDEX({Great Lakes Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review Range 2}, 0))), INDEX({HI Region Review Range 1}, MATCH([RE Store # Lookup]@row, {HI Region Review Range 2}, 0))), INDEX({NE and NYC Review Range 1}, MATCH([RE Store # Lookup]@row, {NE and NYC Review Range 2}, 0))), INDEX({NW Region Review Range 1}, MATCH([RE Store # Lookup]@row, {NW Region Review Range 2}, 0))), INDEX({South Region Review Range 1}, MATCH([RE Store # Lookup]@row, {South Region Review Range 2}, 0))), INDEX({Southern OH Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Southern OH Region Review Range 2}, 0))), INDEX({TX Region Review Range 1}, MATCH([RE Store # Lookup]@row, {TX Region Review Range 2}, 0)))

    Should be this if you want to display "" when no INDEX/MATCH works.

    Hope it helped!

  • Robert Francher
    Robert Francher ✭✭✭✭

    This forum is the best place ever. I'm testing this out on a small number of records. In the screenshot below, Store # 3201 does not have a match but 4339 and 4341 do. With the new formula, all three are blank:

    If I plug in the single INDEX/MATCH for the sheet where I know there's a match, it brings back a date:

    Any ideas? Thanks again for your time.

    R

  • Robert Francher
    Robert Francher ✭✭✭✭
    Answer ✓

    I co-worker helped me figure this out. Here's the final formula:

    =IFERROR(INDEX({CA Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {CA Region Review - Store #}, 0)), 

    IFERROR(INDEX({Carolinas Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review - Store #}, 0)), 

    IFERROR(INDEX({Central Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Central Region Review - Store #}, 0)), 

    IFERROR(INDEX({FL Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {FL Region Review - Store #}, 0)), 

    IFERROR(INDEX({Great Lakes Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review - Store #}, 0)), 

    IFERROR(INDEX({HI Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {HI Region Review - Store #}, 0)), 

    IFERROR(INDEX({NE and NYC Review - Store Exit Date}, MATCH([RE Store # Lookup]@row, {NE and NYC Review - Store #}, 0)), 

    IFERROR(INDEX({NW Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {NW Region Review - Store #}, 0)), 

    IFERROR(INDEX({South Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {South Region Review - Store #}, 0)), 

    IFERROR(INDEX({Southern OH Region - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Southern OH Region - Store #}, 0)), 

    IFERROR(INDEX({TX Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {TX Region Review - Store #}, 0)), "")))))))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!