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

Find more posts tagged with

Formulas

Accepted answers

All comments

Genevieve P.

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

DaveCzk

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.