Using nested INDEX/MATCH

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

Best Answer

Answers

  • Robert Francher
    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.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!