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
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?
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?
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.
Nevermind, I resolved it!<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"}]},{"discussionID":107817,"type":"question","name":"How to get the formulas to correctly count the completed tasks?","excerpt":"The current formula counts every task for the total. =COUNTIF(DESCENDANTS([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\" I have been trying to get it to only count when all subtask from its proper row have been completed. There are 4 tasks and over 60 subtasks. When the subtasks are…","snippet":"The current formula counts every task for the total. =COUNTIF(DESCENDANTS([Planning Done]@row), 1) + \" of \" + COUNT(CHILDREN([Task\/Topic]@row)) + \" Done\" I have been trying to get…","categoryID":322,"dateInserted":"2023-07-19T19:04:08+00:00","dateUpdated":"2023-07-19T19:16:18+00:00","dateLastComment":"2023-07-19T19:40:18+00:00","insertUserID":163690,"insertUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163690,"lastUserID":163690,"lastUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":43,"score":null,"hot":3379592666,"url":"https:\/\/community.smartsheet.com\/discussion\/107817\/how-to-get-the-formulas-to-correctly-count-the-completed-tasks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107817\/how-to-get-the-formulas-to-correctly-count-the-completed-tasks","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107817,"commentID":386077,"name":"Re: How to get the formulas to correctly count the completed tasks?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386077#Comment_386077","dateInserted":"2023-07-19T19:40:18+00:00","insertUserID":163690,"insertUser":{"userID":163690,"name":"Ruvalcaba12","url":"https:\/\/community.smartsheet.com\/profile\/Ruvalcaba12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T20:44: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2N6H1QH5FWDV\/screenshot.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"ScreenShot.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-19T19:24:31+00:00","dateAnswered":"2023-07-19T19:19:24+00:00","acceptedAnswers":[{"commentID":386066,"body":"
Genevieve P. Employee Admin
L_123 ✭✭✭✭✭✭
