Help with INDEX MATCH with duplicate unique identifiers

xnickeddlemanx
xnickeddlemanx ✭
inFormulas and Functions

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

image.png
image.png


Thanks!

Tags:
· Share on Facebook Share on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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 theCohort #column in the source sheet, and the {LP3 Learn Completions Range 8} is theSSOcolumn, is that correct?

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

    Thanks!

    Genevieve

    · Share on Facebook Share on Twitter
  • xnickeddlemanx
    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

    · Share on Facebook Share on Twitter
  • L_123
    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.

    · Share on Facebook Share on Twitter
  • xnickeddlemanx
    xnickeddlemanx ✭

    [email protected]

    · Share on Facebook Share on Twitter
  • L_123
    L_123 ✭✭✭✭✭✭

    ?

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
@GHustad1<\/a> It's easiest to check for blank cells in the range, then sum if there are none, like this:<\/p>


<\/p>

=IF(COUNTIF([Retail \/ Non-Retail Helper Column]@row:[Resources Required - Helper Column]@row, ISBLANK(@cell)) = 0, SUM([Retail \/ Non-Retail Helper Column]@row:[Resources Required - Helper Column]@row), \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>


<\/p>

The formula counts the number if blank cells in the range, if there is at least one, the count will NOT equal 0, and it will insert \"//www.santa-greenland.com/community/discussion/comment/\", else if there are values in all cells, it sums the range.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108059,"type":"question","name":"Rolling 12 Months using AVG COLLECT formula help","excerpt":"Hi, I am looking for some help. I am using the above SUMIFS, however doing an AVG COLLECT with two criteria. I can get the above formula to work for 6 months, however I am at a loss to get it to work for 12 months in the past. Below is my current formula and would appreciate any help. =ROUND(AVG(COLLECT({Data days from…","snippet":"Hi, I am looking for some help. I am using the above SUMIFS, however doing an AVG COLLECT with two criteria. I can get the above formula to work for 6 months, however I am at a…","categoryID":322,"dateInserted":"2023-07-25T19:33:59+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T21:02:29+00:00","insertUserID":133636,"insertUser":{"userID":133636,"name":"Tina Davis","url":"https:\/\/community.smartsheet.com\/profile\/Tina%20Davis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-25T21:05:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":133636,"lastUser":{"userID":133636,"name":"Tina Davis","url":"https:\/\/community.smartsheet.com\/profile\/Tina%20Davis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-25T21:05:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":31,"score":null,"hot":3380635588,"url":"https:\/\/community.smartsheet.com\/discussion\/108059\/rolling-12-months-using-avg-collect-formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108059\/rolling-12-months-using-avg-collect-formula-help","format":"Rich","lastPost":{"discussionID":108059,"commentID":387021,"name":"Re: Rolling 12 Months using AVG COLLECT formula help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387021#Comment_387021","dateInserted":"2023-07-25T21:02:29+00:00","insertUserID":133636,"insertUser":{"userID":133636,"name":"Tina Davis","url":"https:\/\/community.smartsheet.com\/profile\/Tina%20Davis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-25T21:05:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-25T21:02:37+00:00","dateAnswered":"2023-07-25T20:56:32+00:00","acceptedAnswers":[{"commentID":387019,"body":"

I provided a link in my last comment. Is it not working?<\/p>


<\/p>

Basically I suggested that you use 365 days (or -183 to +183) instead of trying to use dates.<\/p>


<\/p>

AND(@cell>= TODAY(-183), @cell<= TODAY(183))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108053,"type":"question","name":"IF(JOIN(COLLECT Statement","excerpt":"Hello! I am using this formula to collect a set of ID numbers based on a criteria. =JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), \" \") This gives me exactly what I need, but I want to wrap it in an IF function so that if the criteria is not met, the cell will say \"No\" This is what I've been trying…","snippet":"Hello! I am using this formula to collect a set of ID numbers based on a criteria. =JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), \" \") This gives me…","categoryID":322,"dateInserted":"2023-07-25T18:34:54+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T20:04:05+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-25T22:27:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":162305,"lastUser":{"userID":162305,"name":"Cory Page","title":"Revenue Cycle Analyst & Informatics","url":"https:\/\/community.smartsheet.com\/profile\/Cory%20Page","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-25T20:39:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":38,"score":null,"hot":3380628539,"url":"https:\/\/community.smartsheet.com\/discussion\/108053\/if-join-collect-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108053\/if-join-collect-statement","format":"Rich","lastPost":{"discussionID":108053,"commentID":387001,"name":"Re: IF(JOIN(COLLECT Statement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387001#Comment_387001","dateInserted":"2023-07-25T20:04:05+00:00","insertUserID":162305,"insertUser":{"userID":162305,"name":"Cory Page","title":"Revenue Cycle Analyst & Informatics","url":"https:\/\/community.smartsheet.com\/profile\/Cory%20Page","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-25T20:39:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-25T20:07:10+00:00","dateAnswered":"2023-07-25T19:27:02+00:00","acceptedAnswers":[{"commentID":386993,"body":"

Ok, I think I see whats going on there is no error when nothing is found so things are coming out blank. <\/p>

This should let you say no when the collect finds nothing, sorry I probably should have just built the formula and tested it real quick. <\/p>

Updated: I had to add your original Join Collect its way easier for you to review that was I would think. <\/p>

=IF(JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), \" \") = \"//www.santa-greenland.com/community/discussion/comment/\", \"No\", JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), \" \")))<\/p>

If you translate this to your sheet it should do the trick I would think.. <\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Sumifs formula only if all columns contain a value

    I have sheet that is "scoring" projects across 6 criteria. The criteria all have numeric values associated with them from 1-4. I want to sum the values for all the criteria, but ONLY IF all columns have a value. Meaning, if someone fails to score one of the criteria, I want the sum column to be blank vs it summing the…

  • Rolling 12 Months using AVG COLLECT formula help

    Hi, I am looking for some help. I am using the above SUMIFS, however doing an AVG COLLECT with two criteria. I can get the above formula to work for 6 months, however I am at a loss to get it to work for 12 months in the past. Below is my current formula and would appreciate any help. =ROUND(AVG(COLLECT({Data days from…

  • IF(JOIN(COLLECT Statement

    Hello! I am using this formula to collect a set of ID numbers based on a criteria. =JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " ") This gives me exactly what I need, but I want to wrap it in an IF function so that if the criteria is not met, the cell will say "No" This is what I've been trying…