Issue with cross-sheet =INDEX(MATCH()) formulas
I am consistently coming across an error where my cross-sheet referenced =INDEX(MATCH()) formulas are pulling the incorrect information. I am fairly certain my syntax is correct and that this is a bug. This is happening across multiple sheets I have. I have included one example, but note that I have found this to happen across the board.
Here's what I suspect might be happening:
1) formula is weirdly interpreting dash symbols ("-")
2) formula is not correctly looking for exact matches, but rather partial matches.
3) formula is not always matching characters sequentially/in the right sequence.
Example:
I have two sheets: Product Codes and 2024 Guided Ops Metrics.
In 2024 Guided Ops Metrics, I am trying to populate the "Unique Identifier #" column from the "Product Codes" Spreadsheet, where the TourCode & Year @ row (Ops Metrics) matches the TourCode & Year (Product Codes).
In the Product Codes Spreadsheet, I have our 2023 and 2024 products built in the "TourCode & Year" column. Each value in the Product Codes "TourCode & Year" column is unique - there are no duplicates. Example of how they are formatted:
ACB-2023
ACB-2024
FR-NORMBRIT-2023
FR-NORMBRIT-2024
Here is the formula I used:
=INDEX({Product Codes Tour Year & Code Unique Identifier}, MATCH([TourCode & Year]@row, {Product Codes TourID and Year}),0)
This seems to be incorrectly matching the data between the two sheets. Everything that I have referenced tells me that the syntax is correct (please tell me if it's not!)
When I am trying to pull based on TourCode & Year matching ACB-2024, (WHERE Product Codes.[TourCode & Year] = Ops Metrics. [TourCode & Year]), it is incorrectly pulling the information for ACB-2023.
Similarly, for our TourCodes whose formatting contains two dashes (ex. FR-NORMBRIT-2024), it is incorrectly matching. It seems to be matching these to the highest cell on the sheet that matches first couple of characters in the cell.
I have tried reversing the position of the year and tour code on both sheets (example: 2024-ACB or 2024-FR-NORMBRIT) but it still incorrectly matches the Tour Codes formatted with two dashes.
I have tried the following (among other things) to troubleshoot:
- "Manage References" > clearing all references and re-writing the formulas,
- Checked that match formula was referencing the whole column on ProductCodes spreadsheet
- Copied and pasted exact values from sheet one to sheet two to ensure they exactly match. (ex. copying "ACB-2024" from my target sheet to my referenced sheet in the appropriate cell to ensure they were exactly identical.
- Switched the position of the year (ex. -2024 vs 2024-)
- Referenced and read through following Smartsheet pages: "INDEX Function," "MATCH function," "Formula combinations for cross-sheet references," "INDEX and MATCH across two sheets: a detailed explanation," and "Video Index Match Tutorial."
- Sent help ticket to Smartsheet. Recieved only generic "have you references these resources?" response and did not respond when I replied I had.
Is this a bug? If it is a bug, who can I reach out to to get it fixed? Am I misinterpreting the correct usage of =INDEX(MATCH()), or using incorrect syntax? Has anybody come across this and/or have a solution/workaround?
Cheers,
Emma
Best Answer
-
Paul Newcome ✭✭✭✭✭✭
The problem is with a misplaced parenthesis. That zero at the end is technically part of the INDEX function, but you want it as part of the MATCH function to denote an exact match.
You have:
INDEX(.........MATCH(...........), 0)
You want:
INDEX(.........MATCH(..........., 0))
thinkspi.com
Answers
-
StevenBlackburnMBA ✭✭✭✭
I see the potential issue but I'm not too sure yet and want to try to replicate it on my end.
Can you send the formula copy and paste here so I can manipulate it a tad, I believe it may have something to do with your range but will verify.
Are you able to send a picture of how you selected your ranges in the cross sheet reference?
-
StevenBlackburnMBA ✭✭✭✭
Also, would be glad to help interpret... could hop on a call and assist or am willing to be shared on a duplicate of the sheets in a separate workspace with all unnecessary and non needed information deleted from the worksheet? That might be a good way to see your reference ranges and how you have this sorted. Please let me know, I'd love to help.
-
egardner ✭
Hi Steven,
Thanks for getting back to me.
I've created a workspace that I can share with you with two scrubbed versions of the documents, if that's helpful. Let me know how you'd like me to go about sharing that with you. Also more than happy to hop on a call.
Here's the formula (from the scrubbed version):
=指数({产品代ob欧宝娱乐app手机下载码惟一标识符}匹配([TourCode & Year]@row, {Product Codes Tour Code & Year}), 0)
I normally just select the entire column when selecting the range. I've also tried selecting all populated cells (using shift + click) instead of clicking the column header, but it made no difference.
I've also re-named the selection ranges just for my own purposes. In troubleshooting, I also tried going in and deleting the reference ranges, re-doing the formulas with the default range names to see if it made a difference, but still got the same result.
See below for photos of the referencing process.
Thanks so much for your help!
Emma
-
Paul Newcome ✭✭✭✭✭✭
The problem is with a misplaced parenthesis. That zero at the end is technically part of the INDEX function, but you want it as part of the MATCH function to denote an exact match.
You have:
INDEX(.........MATCH(...........), 0)
You want:
INDEX(.........MATCH(..........., 0))
thinkspi.com
-
egardner ✭
Hi Paul,
I had tried that originally when troubleshooting and it didn't work. I just tried that again and that fixed it. Thank you so much!
-
Paul Newcome ✭✭✭✭✭✭
Categories
=IF([Date as Text Type]@row <> \"//www.santa-greenland.com/community/discussion/106962/\", DATE(VALUE(LEFT([Date as Text Type]@row, 4)), VALUE(MID([Date as Text Type]@row, 6, 2)), VALUE(MID([Date as Text Type]@row, 9, 2))), \"//www.santa-greenland.com/community/discussion/106962/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":107081,"type":"question","name":"Users having issues with accessing published sheets","excerpt":"HI I have had several users reach out to me indicating they are no longer able to access a published sheet. They have been able to access in the past but now they are not. Nothing has changed with these published. Yesterday for one user, I deleted their account and re-added. Once he accepted the invitation, he was able to…","snippet":"HI I have had several users reach out to me indicating they are no longer able to access a published sheet. They have been able to access in the past but now they are not. Nothing…","categoryID":321,"dateInserted":"2023-06-29T14:13:17+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T14:47:23+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-29T14:43:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T19:12:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3376099840,"url":"https:\/\/community.smartsheet.com\/discussion\/107081\/users-having-issues-with-accessing-published-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107081\/users-having-issues-with-accessing-published-sheets","format":"Rich","tagIDs":[219,223],"lastPost":{"discussionID":107081,"commentID":383099,"name":"Re: Users having issues with accessing published sheets","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383099#Comment_383099","dateInserted":"2023-06-29T14:47:23+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T19:12:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/BO5O2K273I0W\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T14:45:56+00:00","dateAnswered":"2023-06-29T14:43:11+00:00","acceptedAnswers":[{"commentID":383094,"body":"
Does your company use Single Sing-On? There were some updates that took affect yesterday. I wonder if maybe that is affecting things since it seems to be an issue with being part of the company's account.<\/p>
<\/p>