Index Match Across Multiple Sheets
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!
Best Answer
-
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
-
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)))
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!