Using IFERROR to reference 2 sheets
Using the formula below to pull references from two sheets. I am getting data returned from the first sheet, but not the second. When I reverse the order of the references the formula works the other way around. Do I have an extra bracket or something causing it not to index past the first sheet?
=IFERROR(INDEX({Milestone Data - Store Name}, MATCH([Store #]@row, {Milestone Data Range - Store #}, 0)), IFERROR(INDEX({North America Store Attributes- Store Name}, MATCH([Store #]@row, {North America Store Attributes- Store #}))))
Answers
-
Hi @DaveCzk
Right now your formula says, complete this formula, but if there's an error, go to the other formula instead. It sounds like you're not getting an error from the first sheet, so it won't check the second sheet.
Instead, are you looking to have both results returned? If so, you could use + to add together multiple formulas.
Try:
=IFERROR(INDEX({Milestone Data - Store Name}, MATCH([Store #]@row, {Milestone Data Range - Store #}, 0)), "") + " / " + IFERROR(INDEX({North America Store Attributes- Store Name}, MATCH([Store #]@row, {North America Store Attributes- Store #})), "")
Notice that I added a / to separate the two values, but you could include something else, for example a "Sheet One" or "Sheet Two" in front of each value returned:
="Sheet One: " + IFERROR(INDEX({Milestone Data - Store Name}, MATCH([Store #]@row, {Milestone Data Range - Store #}, 0)), "No Match") + " || Sheet Two: " + IFERROR(INDEX({North America Store Attributes- Store Name}, MATCH([Store #]@row, {North America Store Attributes- Store #})), "No Match")
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi, I actually found another way to make it work, I removed the second instance of "IFERROR" in the formula and it is now working. This is the formula that returns the result I am looking for:
=IFERROR(INDEX({Milestone Data - Store Name}, MATCH([Store #]@row, {Milestone Data Range - Store #}, 0)), (INDEX({North America Store Attributes- Store Name}, MATCH([Store #]@row, {North America Store Attributes- Store #}, 0))))
Now if it returns an error in the first sheet, it moves to the second.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!