Index Match Across Multiple Sheets

Options
Nancy Heater
Nancy Heater ✭✭✭✭
edited 11/30/22 in Formulas and Functions

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!

Tags:

Best Answer

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Answer ✓
    Options

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

Answers

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Answer ✓
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!