Index Match Across Multiple Sheets

Nancy Heater

Hello Smartsheet Community!

I have reviewed a few similar posts on this subject, but have further questions ...

Examples shown on other discussion threads, show to use stacked IFERRORs, and repeated Index/Match in the formula to look at multiple sheets, however I am not able to get this to work.

When you're stacking IFERROR's, do the number of IFERRORS need to equal the number of sheets the formula is referencing?

I am trying to do an Index/Match with 5 different sheets, and the number of sheets will grow year after year.

Example below with formula "broken out" to be easier to read.

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR

(INDEX({Sheet1 Range 1},MATCH(Synth@row,{Sheet1 Range 2},0)),

INDEX({Sheet2 Range 1},MATCH(Synth@row,{Sheet2 Range2},0))),

INDEX({Sheet3 Range 1},MATCH(Synth@row,{Sheet3 Range 2},0))),

INDEX({Sheet4 Range 1},MATCH(Synth@row,{Sheet4 Range 2},0))),

INDEX({Sheet5 Range 1},MATCH(Synth@row,{Sheet5 Range 2},0)))

Thank you in advance for the assistance!

Nancy Heater

Thru a process of elimination, I was able to figure this out. It still boggles my mind, but it worked:

=IFERROR(IFERROR(IFERROR(IFERROR

(INDEX({Sheet1 Range 1},MATCH(Synth@row,{Sheet1 Range 2},0)),

INDEX({Sheet2 Range 1},MATCH(Synth@row,{Sheet2 Range2},0))),

INDEX({Sheet3 Range 1},MATCH(Synth@row,{Sheet3 Range 2},0))),

INDEX({Sheet4 Range 1},MATCH(Synth@row,{Sheet4 Range 2},0))),

INDEX({Sheet5 Range 1},MATCH(Synth@row,{Sheet5 Range 2},0)))

written out properly:

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX({Sheet1 Range 1}, MATCH(Synth@row,{Sheet1 Range 2},0)), INDEX({Sheet2 Range 1}, MATCH(Synth@row,{Sheet2 Range2},0))), INDEX({Sheet3 Range 1}, MATCH(Synth@row,{Sheet3 Range 2},0))), INDEX({Sheet4 Range 1}, MATCH(Synth@row,{Sheet4 Range 2},0))), INDEX({Sheet5 Range 1}, MATCH(Synth@row,{Sheet5 Range 2},0)))

Nancy Heater

Thru a process of elimination, I was able to figure this out. It still boggles my mind, but it worked:

=IFERROR(IFERROR(IFERROR(IFERROR

(INDEX({Sheet1 Range 1},MATCH(Synth@row,{Sheet1 Range 2},0)),

INDEX({Sheet2 Range 1},MATCH(Synth@row,{Sheet2 Range2},0))),

INDEX({Sheet3 Range 1},MATCH(Synth@row,{Sheet3 Range 2},0))),

INDEX({Sheet4 Range 1},MATCH(Synth@row,{Sheet4 Range 2},0))),

INDEX({Sheet5 Range 1},MATCH(Synth@row,{Sheet5 Range 2},0)))

written out properly:

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX({Sheet1 Range 1}, MATCH(Synth@row,{Sheet1 Range 2},0)), INDEX({Sheet2 Range 1}, MATCH(Synth@row,{Sheet2 Range2},0))), INDEX({Sheet3 Range 1}, MATCH(Synth@row,{Sheet3 Range 2},0))), INDEX({Sheet4 Range 1}, MATCH(Synth@row,{Sheet4 Range 2},0))), INDEX({Sheet5 Range 1}, MATCH(Synth@row,{Sheet5 Range 2},0)))

Genevieve P.

Hey @Nancy Heater

Thanks for posting your solution! Looks great.

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.

Cheers,

Genevieve