Trying to index match a formula for multiple sheets

Max Ahmed

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

Paul McGuinness

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

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.

Mike TV

@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

Mike I tried that and still get incorrect argument set.

Paul McGuinness

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

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

Paul that second one Worked Great!

Colleen Jones

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

JenniferN

What would it look like to match from three sheets?

JenniferN

NVM, I found it on another discussion "As you've found, with nested IFERROR statements, you need one less IFERROR function per formula. This is because the final formula is the result if there isn't an error."