index match formula not working
Hi, I am using the below index match formula which is not working. The MFG Rev 18 area range is a "contact list" column type, and the column used for the formula is a Text type. I assume the formula is not working due to the type, is there another way around this?
=INDEX({Mfg Rev 18 area}, MATCH([Start Date]3, {start date range}, 0))
Thank you,
Dave
Comments
-
J. Craig Williams ✭✭✭✭✭✭What feedback are you getting?
#INCORRECT ARGUMENT SET
#CONTACT COLUMN DOES NOT SUPPORT FORMULAS
#NO MATCH
or something else?
If the formula resides in a Contact List type column, you will get the message that they can't have formulas.
The formula WILL return the name (not email address or full contact info) from the {Mfg Rev 18 area} range, if it finds it.
The range sizes need to be the same.
Also note that your formula will return the first match it finds, which is OK, but is sometimes an odd thing for a Date field. No inconceivable, but odd.
I hope this helps.
Craig
-
David Friesen ✭✭✭
The error is #NO MATCH. The range sizes are the same. There is only one date that matches the reference in the formula. Formula is in Text/Number type. I changed the dates so both were not a formula but a date only. It's a bugger anything I should look at?
-
J. Craig Williams ✭✭✭✭✭✭
Yes.
There are two types of date related columns.
One is the Date column. The other is a Date/Time column. The second are the two columns associated with Project Settings when Dependencies are enabled and the two system columns.
The image below has color coded match formulas.
The first tries to find a DATE in a range of DATE/TIME
=MATCH([email protected], Start$1:Start$4, 0)
The second tries to find a DATE/TIME in a range of DATE
=MATCH([email protected], SD$1:SD$4, 0)
both fail because it is like apples and tomatoes, both red, both fruit, but not the same.
The third forces the DATE/TIME to be a DATE, so it finds a match.
=MATCH(DATEONLY([email protected]), SD$1:SD$4, 0)
I don't know a way to force the range to converted from DATE/TIME to DATE without creating a new column to do so.
Maybe that helps?
Craig
-
David Friesen ✭✭✭
I removed the predecessor in the project settings and it worked. Fortunately there was no need for predecessors on this sheet.
Craig, thanks for your invaluable help and quick replies.
-
J. Craig Williams ✭✭✭✭✭✭
You are welcome.
Craig
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/29676/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":45,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
