Trying to index match across multiple sheets, need assistance

I am trying to index match across multiple sheets. Each sheet is specific to it's own project and I want to index certain values on a master tracker for all projects. Each sheet is broken down by site name and each site has it's own progression. The column name for the site is named Site ID and the column name for the progression is called Site Progression. I need to index the progression, but match the site ID's. Currently I have this formula I am trying:

=INDEX({ATC TMO CX Tracker Site Progression}, MATCH([Site ID]@row, {ATC TMO CX Tracker Site ID}, 0)), INDEX({MasTec CX Tracker Site Progression}, MATCH([Site ID]@row, {MasTec CX Tracker Site ID}, 0)), INDEX({SBA TMO CX Tracker Site Progression}, MATCH([Site ID]@row, {SBA TMO CX Tracker Site ID}, 0))

I can get it to work on one line (index matching to one sheet, however, I cannot figure out how to get this to work so that I can index match across multiple sheets. Is this even possible? Can anyone please help?

Find more posts tagged with

Accepted answers

All comments

KPH

If the match is only true in one sheet you can combine the indexes with IFERROR. In other words start an IFERROR function, put one INDEX MATCH in it, in the value-if-error place put another IFERROR with the next INDEX MATCH and repeat. Essentially you will be trying an INDEX if no match is found the IFERROR will send you to the next INDEX. If no match there, the next will be tried, etc.

If there are matches on multiple sheets and you want to return them all in one cell, you can use JOIN COLLECT.

tblocker

Thank you for this, I will give it a go and see if I can get it working!

tblocker

For any that may come here for a solution, this is what I ended up getting to work:

=IFERROR(INDEX({Site Progression Helper Range 1}, MATCH(Project@row, {Site Progression Helper Range 2}, 0)), IFERROR(INDEX({Site Progression Helper Range 3}, MATCH(Project@row, {Site Progression Helper Range 4}, 0)), IFERROR(INDEX({Site Progression Helper Range 5}, MATCH(Project@row, {Site Progression Helper Range 6}, 0)), "")))

KPH

Looks good @tblocker , glad I could help

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.