Index/Match

I have two separate sheets that reference an employee roster, they are separated due to being different vendor company and employees on each sheet. They have the following data points.

Employee Name

Employee ID

Position

We have a roster being used by our supervisors that references details in either of these sheets when they enter a name into a specific field. I am currently using index/match on the roster to look up their Employee ID and Position. This is working perfectly; however since both "entities" are not on one sheet its giving me "no match" results for the employees not reference on the currently referenced sheet

The question is can I stack index/match function to look in separate sheets to return a matching result. For the most part, one of the sheets should always have a match that i should really never get a "no match" but i need to look and match against both sheets. Sorry if i'm rambling on

The existing function i am using is

=IF(ISBLANK([Officer Assigned]@row), "", INDEX({Roster Template Range 3}, MATCH([Officer Assigned]@row, {Roster Template Range 4}, 0)))

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Davidp

To force the other INDEX/MATCH you can leverage the NoMatch# error using the IFERROR function. This function says execute your normal formula but if you encounter an error, 'do this'. In your case the 'do this' is the other Index/Match.

=IF(ISBLANK([Officer Assigned]@row), "", IFERROR(INDEX({Roster Template Range 3}, MATCH([Officer Assigned]@row, {Roster Template Range 4}, 0)),INDEX({other sheet equivalent to Range 3}, MATCH([Officer Assigned]@row, {other sheet equivalent Range 4},0))))

Will this work for you?

Kelly

Davidp

@Kelly Moore Thank you so much for your incite on this. Your recommendation did the trick. I ran the line several times with no luck so I had to break it down one piece at a time to realize what I was doing wrong. Thank you so much for your assistance. I really enjoy learning this expanding on my skillset of writing functions.

🤩🤩🤩

Kelly Moore

I'm pleased you got it to work and even more pleased that you were able to dissect the formula to expand your own knowledge.

Kelly

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.