INDEX/MATCH not working

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 12/09/19 inFormulas and Functions

Below are two screenshots. The first is a screenshot of my target sheet. The other is a screenshot of my reference sheet.

On the reference sheet I have a list along the left. In the column to the right is:

=TODAY()

On the target sheet is an identical list with the following formula to the right:

=IF(TODAY() = INDEX({New Sheet Test Range 1}, MATCH([Primary Column]@row, {New Sheet Test Range 1}, 0), 2), "Matched Up", "Not Matching")

What this SHOULD do is look on the reference sheet for the value that is in the@rowposition on my target sheet. If it finds that value and the value in the column to the right of that equals TODAY(), then it would say Matched Up. If the date did not equal TODAY(), it would say Not Matching.

Theoretically... They should ALL say "Matched Up" on my target sheet as the reference list is exactly identical and the dates on the reference sheet are populated by =TODAY().Theoretically...

What is actually happening is that half way down my list it suddenly finds that the dates don't match, and in the last row, I have an #INVALID DATA error.

I have copy/pasted, manually typed, and drag filled hoping that maybe it was a typo I had made somewhere or something, but I always get the same result. I have also tried different date variations making sure they matched up hoping to force a match to no avail. It is always in the same positions that it changes too. Halfway down the list it turns to mo match and then the last row is #INVALID VALUE. It doesn't matter if its 26 rows or 100 rows. I've even tried creating rand new sheets. Nothing is working.

Any suggestions?

Capture.PNG

Capture2.PNG

thinkspi.com

Tags:
· Share on Facebook Share on Twitter

Comments

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公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Try a DATE function:<\/p>

=(NETDAYS([Requested Start Date]@row, DATE(2022, 12, 1)<\/strong>) \/ 365) * 12<\/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"}]},{"discussionID":107082,"type":"question","name":"Automatic Update of Dropdown List?","excerpt":"I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet? I can create a helper sheet if that would help. Any ideas? I don't believe I have the premium Smartsheets plan. Thank you! Lori","snippet":"I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet?…","categoryID":322,"dateInserted":"2023-06-29T14:16:13+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T18:02:59+00:00","insertUserID":162337,"insertUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-29T18:08:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-29T18:55:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":48,"score":null,"hot":3376116552,"url":"https:\/\/community.smartsheet.com\/discussion\/107082\/automatic-update-of-dropdown-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107082\/automatic-update-of-dropdown-list","format":"Rich","lastPost":{"discussionID":107082,"commentID":383175,"name":"Re: Automatic Update of Dropdown List?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383175#Comment_383175","dateInserted":"2023-06-29T18:02:59+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-29T18:55:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T14:52:00+00:00","dateAnswered":"2023-06-29T14:50:26+00:00","acceptedAnswers":[{"commentID":383100,"body":"

@maineL<\/a>,<\/p>

I haven't seen this possible without the use of a 3rd party integration. I don't believe it is supported natively in Smartsheet. The question comes up all the time though. <\/p>

BRgds,<\/p>

-Ray<\/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":107083,"type":"question","name":"Excluding data when using COUNTIF","excerpt":"Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, \"Unallocated\") We do not want to pull through any project that is on hold but want to keep the status as it is on this column so have an additional column with the RAG Status…","snippet":"Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, \"Unallocated\") We do not want…","categoryID":322,"dateInserted":"2023-06-29T14:45:28+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T15:24:40+00:00","insertUserID":162412,"insertUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162412,"lastUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3376103408,"url":"https:\/\/community.smartsheet.com\/discussion\/107083\/excluding-data-when-using-countif","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107083\/excluding-data-when-using-countif","format":"Rich","lastPost":{"discussionID":107083,"commentID":383116,"name":"Re: Excluding data when using COUNTIF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383116#Comment_383116","dateInserted":"2023-06-29T15:24:40+00:00","insertUserID":162412,"insertUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T15:24:21+00:00","dateAnswered":"2023-06-29T15:16:19+00:00","acceptedAnswers":[{"commentID":383110,"body":"

@JennyB.<\/a> <\/p>

If I'm understanding your question correctly, you want to counts from one column but want to exclude projects that have an \"On Hold\" status in a second column. If so, then a countifs formula would work:<\/p>

=COUNTIFS({Column to Count}, \"Unallocated\", {Other column}, <>\"On Hold\")<\/p>

Hope this helps!<\/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

  • Calculate the # of months between 2 dates, with 1 date being static

    Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12/1/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…

  • Automatic Update of Dropdown List?

    I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet? I can create a helper sheet if that would help. Any ideas? I don't believe I have the premium Smartsheets plan. Thank you! Lori

  • Excluding data when using COUNTIF

    Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, "Unallocated") We do not want to pull through any project that is on hold but want to keep the status as it is on this column so have an additional column with the RAG Status…