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)

Formula.PNG

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.

ACB,Ops Metrics, Year Last.PNG
Product Codes, Year Last, Incorrectly Pulling ACB.PNG


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.

Ops Metrics, Year Last, Incorrect, FR-NORMBRIT2.PNG


Product Codes, Year Last, Incorrectly Pulling FR-NORMBRIT.PNG


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.

运维指标,年第一,正确,FR-NORMBRIT。PNG
Product Codes, Year First, Incorrectly Pulling, FR-NORMBRIT.PNG


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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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

  • 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?

  • 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.

  • 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.

    image.png
    image.png
    image.png
    image.png
    image.png


    Thanks so much for your help!


    Emma

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.

    thinkspi.com

You will need to make sure your [Date as DateType] column is formatted as Date. This also assumes your [Date as Text Type] column is always in the same format. (Starts with full 10 digit date)<\/p>

=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>

\n \n https:\/\/community.smartsheet.com\/discussion\/106673\/important-be-aware-of-upcoming-changes-to-microsoft-sso-one-time-permissions-consent-required\n <\/a>\n<\/div>\n


<\/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":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":223,"urlcode":"Publish","name":"Publish"}]},{"discussionID":107057,"type":"question","name":"Forms URL query String, IS there a limit to how many queries can be used?","excerpt":"Hi, I am a novice with Smartsheet, but am learning. I have a form that I need to pre-populate so we can send it out to technicians to complete a field report. I can get five fields to populate, but no more than that. =\"https:\/\/app.smartsheet.com\/b\/form\/d2cda8ab647e4c11b40e503977eb916b?Part%20Number=\" + [Part Number]@row +…","snippet":"Hi, I am a novice with Smartsheet, but am learning. I have a form that I need to pre-populate so we can send it out to technicians to complete a field report. I can get five…","categoryID":321,"dateInserted":"2023-06-28T20:33:16+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T04:52:07+00:00","insertUserID":162197,"insertUser":{"userID":162197,"name":"cjg","title":"Service Manager","url":"https:\/\/community.smartsheet.com\/profile\/cjg","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T19:22:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":139601,"lastUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"jmyzk","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-30T09:43:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":19,"countViews":73,"score":1,"hot":3376096823,"url":"https:\/\/community.smartsheet.com\/discussion\/107057\/forms-url-query-string-is-there-a-limit-to-how-many-queries-can-be-used","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107057\/forms-url-query-string-is-there-a-limit-to-how-many-queries-can-be-used","format":"Rich","tagIDs":[204],"lastPost":{"discussionID":107057,"commentID":383259,"name":"Re: Forms URL query String, IS there a limit to how many queries can be used?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383259#Comment_383259","dateInserted":"2023-06-30T04:52:07+00:00","insertUserID":139601,"insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"jmyzk","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-30T09:43:21+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\/0T7JFZO9ETN7\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T15:53:55+00:00","dateAnswered":"2023-06-29T15:20:18+00:00","acceptedAnswers":[{"commentID":383113,"body":"

@cjg<\/a> <\/p>

Excellent!<\/p>

You're more than welcome!<\/p>

Here's an excellent helpful article detailing the special characters that might need to be used to make the form work.<\/p>

Remember! <\/strong>Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up\/Awesome or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":1},{"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":204,"urlcode":"Forms","name":"Forms"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=341&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":5431,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Using Smartsheet