INDEX/MATCH function across more than 2 sheets

Hi,

I have a list of sites and I am try to auto populate the Site type column based on different sheets (Site Index sheet... but each country has one)

I have the formula working to reference just Australia's Site Index Sheet but I need to INDEX all of the countries sheets ...

=INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0))

I tried the below but that doesn't ork:

=INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0)) + INDEX({CEE - Ops Site Index BL}, MATCH([Site Name]@row, {CEE - Ops Site Index Sitename}, 0))

Find more posts tagged with

Accepted answers

All comments

parulmishra

You can use IFERROR to do this.. IFERROR(value, value_if_error).

Something like this

= IFERROR(INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0)),IFERROR(INDEX({CEE - Ops Site Index BL}, MATCH([Site Name]@row, {CEE - Ops Site Index Sitename}, 0)),IFERROR(INDEX.....and so on )

It will try to search for the SiteIndex in Australia and if it doesn't find it in the sheet then it will through "#NOMatch error, so we will put the Index formula for the next BL sheet in the "value_if_error" place. You can keep on doing it for as many sheets you want

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.