Help with INDEX MATCH with duplicate unique identifiers

Hi Community,

I am working with an INDEX MATCH formula to return content from another sheet; however, for each unique value, there are 4 duplicates.

For example, an associate completed 4 learning events all attached to the same cohort name. Records are displayed as:

Associate 123456, Cohort 1, Learning Event 1

Associate 123456, Cohort 1, Learning Event 2

Associate 123456, Cohort 1, Learning Event 3

Associate 123456, Cohort 1, Learning Event 4

When I run the INDEX and MATCH formula, it is making all records as Cohort 1 and the results do not match the other sheet. I have attached screenshots to assist (below) as I am looking to return "Cohort 2" for this particular associate

Thanks!

Find more posts tagged with

Formulas

functionality

Community Platform

Cell linking

Accepted answers

All comments

Genevieve P.

Hi @xnickeddlemanx

I recreated your formula and sheets and it worked as expected. Could you maybe remove the "IFERROR" portion of the formula? It could be useful to see what the error is.

Can you also confirm the ranges in the formula? I presume the {LP3 Learn Completions Range 7} is the Cohort # column in the source sheet, and the {LP3 Learn Completions Range 8} is the SSO column, is that correct?

Final question... what are the column types for the SSO columns in both sheets?

Thanks!

Genevieve

xnickeddlemanx

Hi Genevieve,

When I remove the IFERROR portion, I am still getting #No Match answer. You are correct, the Range 7 is Cohort# and Range 8 is SSO. Both are numbers for the SSO columns in both sheets. Now, one thing that i forgot to mention is that the Cohort # column is a formula, pulling text from another column. I'm using a =MID([Enrollment Event Name]1, 44, 8) to just pull out the cohort #. Would this be causing the issue?

Thanks,

NIck

L_123

Yes. Mid is always going to return a text value, while your number on the other sheet is going to be an integer. Wrap that mid formula in a value and your index match will work.

=value(MID([Enrollment Event Name]1, 44, 8

Do you want to parse all 4 rows instead of just the one? Your formula currently is only going to ever return the first time the SSO returns and ignore the other 3.

xnickeddlemanx

L@123

L_123

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.