Trying to index match a formula for multiple sheets

Options

What's wrong with this formula?

=IFERROR(INDEX({Master Pipeline Name}, MATCH([Store #]@row, {Master Pipeline Store #}, 0), INDEX({Pipeline- OPEN Stores Name}, MATCH([Store #]@row, {Pipeline- OPEN Stores #}, 0) "")

Tags:

Best Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Max Ahmed

    Its difficult to be sure without seeing sheets etc. but it looks like you have too many arguments in your formula.

    Ignoring the IFERROR formula, it appears you have two index match formulas back to back and nothing separating them so smartsheet doesn't know what to pull through.

    =IFERROR(

    INDEX({Master Pipeline Name}, MATCH([Store #]@row, {Master Pipeline Store #}, 0)),

    INDEX({Pipeline- OPEN Stores Name}, MATCH([Store #]@row, {Pipeline- OPEN Stores #}, 0)),

    "")

    If you want the first formula to run and if that fails run the second then the second index match becomes the tail end of the IFERROR formula as below.

    =IFERROR(INDEX({Master Pipeline Name}, MATCH([Store #]@row, {Master Pipeline Store #}, 0)), INDEX({Pipeline- OPEN Stores Name}, MATCH([Store #]@row, {Pipeline- OPEN Stores #}, 0)))

    Not sure if that will fix it without seeing the sheets themselves as could be other reasons for the error but that's what stuck out to me.

    Hope that helps

    Paul

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Here's my suggestion...

    =IFERROR(IFERROR(INDEX({Master Pipeline Name}, MATCH([Store #]@row, {Master Pipeline Store #}, 0)), INDEX({Pipeline- OPEN Stores Name}, MATCH([Store #]@row, {Pipeline- OPEN Stores #}, 0))), "")


    Second IFERROR to output the blank if neither finds a match.

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @Max Ahmed

    In the first INDEX/MATCH you've only closed out the MATCH function and not the first INDEX function. You have the same problem with the 2nd INDEX/MATCH and additionally after the 0) at the end of the 2nd MATCH close you don't have a comma to lead you into your IFERROR function for when there's an error. You need this:

    =IFERROR(INDEX({Master Pipeline Name}, MATCH([Store #]@row, {Master Pipeline Store #}, 0)), INDEX({Pipeline- OPEN Stores Name}, MATCH([Store #]@row, {Pipeline- OPEN Stores #}, 0)), "")

  • Max Ahmed
    Max Ahmed ✭✭✭✭
    Options

    Mike I tried that and still get incorrect argument set.

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Max Ahmed

    Its difficult to be sure without seeing sheets etc. but it looks like you have too many arguments in your formula.

    Ignoring the IFERROR formula, it appears you have two index match formulas back to back and nothing separating them so smartsheet doesn't know what to pull through.

    =IFERROR(

    INDEX({Master Pipeline Name}, MATCH([Store #]@row, {Master Pipeline Store #}, 0)),

    INDEX({Pipeline- OPEN Stores Name}, MATCH([Store #]@row, {Pipeline- OPEN Stores #}, 0)),

    "")

    If you want the first formula to run and if that fails run the second then the second index match becomes the tail end of the IFERROR formula as below.

    =IFERROR(INDEX({Master Pipeline Name}, MATCH([Store #]@row, {Master Pipeline Store #}, 0)), INDEX({Pipeline- OPEN Stores Name}, MATCH([Store #]@row, {Pipeline- OPEN Stores #}, 0)))

    Not sure if that will fix it without seeing the sheets themselves as could be other reasons for the error but that's what stuck out to me.

    Hope that helps

    Paul

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Here's my suggestion...

    =IFERROR(IFERROR(INDEX({Master Pipeline Name}, MATCH([Store #]@row, {Master Pipeline Store #}, 0)), INDEX({Pipeline- OPEN Stores Name}, MATCH([Store #]@row, {Pipeline- OPEN Stores #}, 0))), "")


    Second IFERROR to output the blank if neither finds a match.

  • Max Ahmed
    Max Ahmed ✭✭✭✭
    Options

    Paul that second one Worked Great!

  • Colleen Jones
    Options

    Thanks for this thread! I was able to use it to fix my own formula to index and match from two sheets! I never would have got the IFERROR part without your discussion.

    =IFERROR(INDEX({SIDD Active Projects Solution Type}, MATCH([Project ID]@row, {SIDD Active Projects Project ID Source}, 0)), INDEX({SIDD ARCHIVE Solution Type}, MATCH([Project ID]@row, {SIDD ARCHIVE Source}, 0)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!