Nesting INDEX/MATCH

Robert Francher · 10/02/20

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

Robert Francher · 10/15/20

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)), "")))))))))))

David Joyeuse · 10/03/20

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 · 10/03/20

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 · 10/05/20

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 · 10/07/20

@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 · 10/07/20

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 · 10/15/20