Using nested INDEX/MATCH

Robert Francher

I'm trying to nest an INDEX/MATCH to search multiple sheets for a unique Store #. In my test, I'm only trying to search two different sheets but eventually will need to look in 10 different places. I looked at a previous posting and tried to duplicate the same but I'm getting #UNPARASABLE

Here's what I tried:

=IFERROR(INDEX{Hawaii Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Hawaii Store Cleaning Supplies Range 2}, 0)), INDEX({Texas Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Texas Store Cleaning Supplies Range 2}, 0))

And:

=IFERROR(IFERROR(INDEX{Hawaii Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Hawaii Store Cleaning Supplies Range 2}, 0)), INDEX({Texas Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Texas Store Cleaning Supplies Range 2}, 0))

Not sure what I'm doing wrong. Any ideas?

Thanks,

Robert

Genevieve P.

Hi @Robert Francher

I just tested your first formula and it worked as expected; the only thing I changed was I made sure there was an open parentheses after your first INDEX, before {Hawaii...:

=IFERROR(INDEX({Hawaii Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Hawaii Store Cleaning Supplies Range 2}, 0)), INDEX({Texas Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Texas Store Cleaning Supplies Range 2}, 0))

^Try it again, with the ( in place?

Cheers!

Genevieve P.

Hi @Robert Francher

I just tested your first formula and it worked as expected; the only thing I changed was I made sure there was an open parentheses after your first INDEX, before {Hawaii...:

=IFERROR(INDEX({Hawaii Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Hawaii Store Cleaning Supplies Range 2}, 0)), INDEX({Texas Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Texas Store Cleaning Supplies Range 2}, 0))

^Try it again, with the ( in place?

Cheers!

Robert Francher

Oh my gosh. I looked at this for over an hour the other day. Thank you so much. Here's my final formula which works great ...

=IFERROR(IFERROR(INDEX({Hawaii Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Hawaii Store Cleaning Supplies Range 2}, 0)), INDEX({Texas Store Cleaning Supplies Range 1}, MATCH([Column3]7, {Texas Store Cleaning Supplies Range 2}, 0))), "")

You're the best!

Genevieve P.

Amazing formula!! So glad you could get it working. Sometimes it just takes a set of fresh eyes to catch something small like that. 🙂