Issue with cross-sheet =INDEX(MATCH()) formulas

egardner
egardner ✭
inSmartsheet Basics

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.

2024年带领运维指标,我试图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

· Share on Facebook Share on Twitter

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

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

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

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

    · Share on Facebook Share on Twitter
  • egardner
    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):

    =INDEX({Product Codes Unique Identifier}, MATCH([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

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

    10xViz.com

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

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.️

    10xViz.com

    · Share on Facebook Share on Twitter

Categories

\n \n https:\/\/community.smartsheet.com\/discussion\/108880\/recover-a-row-that-was-accidentally-moved-then-deleted\n <\/a>\n<\/div>\n

Hey @GlennJo<\/a>,<\/p>

the sheet should be available in the owner of the sheet, deleted files. If it is specifically a row of data that was deleted, you can try checking the activity log to see if it shows the removed data there.<\/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":108822,"type":"question","name":"Activity Log | Rows Deleted Filter","excerpt":"I've got a conundrum on my hands where a record has gone missing. In the Activity Log, I'm able to find the record of when it was created. However, there is nothing I'm seeing indicating that it was deleted (even though the Activity Log doesn't have a specific filter for this, I traced through the log for the past 11 days…","snippet":"I've got a conundrum on my hands where a record has gone missing. In the Activity Log, I'm able to find the record of when it was created. However, there is nothing I'm seeing…","categoryID":321,"dateInserted":"2023-08-10T21:46:08+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T22:01:16+00:00","insertUserID":121055,"insertUser":{"userID":121055,"name":"Jake Gustafson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jake%20Gustafson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-lnW91tuGTw!bO9UmyNQSXQ!WUYBUm0HF6t","dateLastActive":"2023-08-11T20:29:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":121055,"lastUser":{"userID":121055,"name":"Jake Gustafson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jake%20Gustafson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-lnW91tuGTw!bO9UmyNQSXQ!WUYBUm0HF6t","dateLastActive":"2023-08-11T20:29:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":37,"score":null,"hot":3383410044,"url":"https:\/\/community.smartsheet.com\/discussion\/108822\/activity-log-rows-deleted-filter","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108822\/activity-log-rows-deleted-filter","format":"Rich","tagIDs":[447],"lastPost":{"discussionID":108822,"commentID":390131,"name":"Re: Activity Log | Rows Deleted Filter","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390131#Comment_390131","dateInserted":"2023-08-10T22:01:16+00:00","insertUserID":121055,"insertUser":{"userID":121055,"name":"Jake Gustafson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jake%20Gustafson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-lnW91tuGTw!bO9UmyNQSXQ!WUYBUm0HF6t","dateLastActive":"2023-08-11T20:29:49+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/SIFFD1TSN3Q6\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T22:01:20+00:00","dateAnswered":"2023-08-10T22:01:16+00:00","acceptedAnswers":[{"commentID":390131,"body":"

Ok, I found it. Classic case of submit something for help, then find the answer. I think what may have happened previously, is that I turned on all the filters, but since there isn't one for a Deleted Row, maybe the Deleted Row was hidden. When I came back, I didn't click anything on the filters for the Activity Log and when scrolling through looking for the 'magic' delete word, I found the record I was looking for.<\/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":447,"urlcode":"activity-log","name":"Activity Log"}]},{"discussionID":108801,"type":"question","name":"Adding users to our account if they are already licensed on another account","excerpt":"Hiya, Couldn't find an answer to my exact question, so hoping someone can help. I am working with an external client to my company, and need to add them to our account on paid licenses. 1) Can I add them to our account if they have a different email address to our company? 2) If they already have an existing licensed…","snippet":"Hiya, Couldn't find an answer to my exact question, so hoping someone can help. I am working with an external client to my company, and need to add them to our account on paid…","categoryID":321,"dateInserted":"2023-08-10T16:26:57+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T17:22:07+00:00","insertUserID":128877,"insertUser":{"userID":128877,"name":"Holly Benjamin","url":"https:\/\/community.smartsheet.com\/profile\/Holly%20Benjamin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T17:23:19+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-08-11T20:31:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":29,"score":null,"hot":3383373544,"url":"https:\/\/community.smartsheet.com\/discussion\/108801\/adding-users-to-our-account-if-they-are-already-licensed-on-another-account","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108801\/adding-users-to-our-account-if-they-are-already-licensed-on-another-account","format":"Rich","tagIDs":[543],"lastPost":{"discussionID":108801,"commentID":390085,"name":"Re: Adding users to our account if they are already licensed on another account","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390085#Comment_390085","dateInserted":"2023-08-10T17:22:07+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-08-11T20:31:01+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T17:23:30+00:00","dateAnswered":"2023-08-10T17:22:07+00:00","acceptedAnswers":[{"commentID":390085,"body":"

They can be added if they use a different domain.<\/p>


<\/p>

If they are licensed on another account, they would need to either be first removed from that account (can cause some logistical headaches) or added as an unlicensed user on your account.<\/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":543,"urlcode":"licensed-users","name":"licensed users"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&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":4940,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics

  • Recover a Row that was accidentally moved then deleted

    I was attempting to copy a row from sheet 1 to sheet 2, but I accidentally moved the row then deleted it, but then realized belatedly that the original (now deleted) data in sheet 1 had been moved. Apparently one can not simply use the "undo action" arrow after moving something--the option is grayed out as in not…

  • Activity Log | Rows Deleted Filter

    I've got a conundrum on my hands where a record has gone missing. In the Activity Log, I'm able to find the record of when it was created. However, there is nothing I'm seeing indicating that it was deleted (even though the Activity Log doesn't have a specific filter for this, I traced through the log for the past 11 days…

  • Adding users to our account if they are already licensed on another account

    Hiya, Couldn't find an answer to my exact question, so hoping someone can help. I am working with an external client to my company, and need to add them to our account on paid licenses. 1) Can I add them to our account if they have a different email address to our company? 2) If they already have an existing licensed…

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

 Watch video