索引/匹配不工作

保罗新来的
保罗新来的 ✭✭✭✭✭✭
编辑12/09/19 公式和函数

下面是两个截图。第一个是我的目标表的截图。另一个是我的参考表的截图。

在参考资料表上,我在左边列出了一个列表。右边一栏是:

=今天()

在目标表单上有一个相同的列表,右侧有以下公式:

=IF(TODAY() = INDEX({New Sheet Test Range 1}), MATCH([Primary Column])@row,{新测试范围1},0),2),"匹配","不匹配")

这应该做的是查看参考表中的值@row我的目标表上的位置。如果它发现该值并且该值右边的列中的值等于TODAY(),那么它将显示Matched Up。如果日期不等于TODAY(),它将显示not Matching。

从理论上讲……他们都应该在我的目标表上说“匹配”,因为参考列表完全相同,参考表上的日期由=TODAY()填充。从理论上讲……

实际发生的情况是,在列表的中间位置,它突然发现日期不匹配,并且在最后一行,我有一个#INVALID DATA错误。

我复制/粘贴,手动输入,并拖动填充希望,也许这是一个打字错误,我做了什么,但我总是得到相同的结果。我还尝试了不同的日期变化,以确保他们匹配,希望强迫匹配无效。它总是在相同的位置,它也在变化。在列表的中间,它变成了mo match,然后最后一行是#INVALID VALUE。不管是26行还是100行。我甚至尝试过创建全新的表单。什么都不起作用。

有什么建议吗?

Capture.PNG

Capture2.PNG

thinkspi.com

评论

帮助文章参考资料欧宝体育app官方888

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
30, {011-AP Archive -# of Days to Complete}, <60) I was able to do this same formula with a COUNTIFS but its' not working the same, what am I missing.","categoryID":322,"dateInserted":"2023-06-16T15:00:58+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156010,"lastUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3373857824,"url":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","format":"Rich","lastPost":{"discussionID":106550,"commentID":381027,"name":"Re: Invalid Operation","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381027#Comment_381027","dateInserted":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+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-16T15:22:31+00:00","dateAnswered":"2023-06-16T15:19:44+00:00","acceptedAnswers":[{"commentID":381026,"body":"

Try this:<\/p>

SUMIFS({011-AP Archive -# of Days to Complete}, {011-AP Archive -# of Days to Complete}, > 30, {011-AP Archive -# of Days to Complete}, < 60)<\/p>

With SUMIFS, you have to include the range to SUM separately from the ranges to compare. In your scenario, they are the same, but can be different.<\/p>

SUMIFS( range, criterion_range1, criterion1, [ criterion_range2, ​criterion2​... ])<\/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":106528,"type":"question","name":"Formula Calculation with a zero value denominator","excerpt":"I'm trying to create a formula to calculate the # Hours per remaining week based off the total HOURS remaining. Calculation takes into consideration the calculated remaining # of weeks on a project. Logic should be: Take Remaining hours \/ # of remaining weeks. If remaining weeks is < 1, simply return the amount of…","categoryID":322,"dateInserted":"2023-06-15T20:54:01+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T16:47:02+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161931,"lastUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":19,"score":null,"hot":3373797663,"url":"https:\/\/community.smartsheet.com\/discussion\/106528\/formula-calculation-with-a-zero-value-denominator","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106528\/formula-calculation-with-a-zero-value-denominator","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106528,"commentID":381042,"name":"Re: Formula Calculation with a zero value denominator","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381042#Comment_381042","dateInserted":"2023-06-16T16:47:02+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/09DN1EF6F1P9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-16T16:47:11+00:00","dateAnswered":"2023-06-16T11:42:38+00:00","acceptedAnswers":[{"commentID":380991,"body":"

Hi @Christine Cao<\/a>,<\/p>

It's down to your formula and rounding. What's happening is that you have 0.3 of a week (which I'm guessing is actually 2 days - 0.2857..., rounded to 0.3). If you calculate 321.5\/(2\/7), you end up with the 1125.3 (to 1 dp).<\/p>

You can get round this by altering your formula to do what you're actually after:<\/p>

=IF([Column2]9 >= 1, [Column3]@row \/ [Column2]9, MAX([Column3]@row, 0))<\/p>

This should make it so if weeks remaining is equal to or greater than 1 it will do the calculation, and otherwise take the MAX of either 0 or column 3.<\/p>

Give this a try and hopefully it should resolve your issue!<\/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":106512,"type":"question","name":"Mandatory \"pick value from list\" field in Global Update","excerpt":"Issue with the Global Update feature, specifically when trying to add a new value to an existing column without altering any existing values. Here's the situation: Objective: Add the value \"Cancelled\" to the \"Status\" column in the Project Plan. Approach: Global Update functionality to ensure this change is reflected across…","categoryID":322,"dateInserted":"2023-06-15T17:51:44+00:00","dateUpdated":"2023-06-15T18:13:35+00:00","dateLastComment":"2023-06-16T11:23:17+00:00","insertUserID":151950,"insertUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":151950,"lastUserID":151950,"lastUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3373767301,"url":"https:\/\/community.smartsheet.com\/discussion\/106512\/mandatory-pick-value-from-list-field-in-global-update","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106512\/mandatory-pick-value-from-list-field-in-global-update","format":"Rich","lastPost":{"discussionID":106512,"commentID":380989,"name":"Re: Mandatory \"pick value from list\" field in Global Update","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380989#Comment_380989","dateInserted":"2023-06-16T11:23:17+00:00","insertUserID":151950,"insertUser":{"userID":151950,"name":"Mirelle Almeida","url":"https:\/\/community.smartsheet.com\/profile\/Mirelle%20Almeida","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hTjmoZuUKqs!clkvh_ZG87s!PpEIw21w9kv","dateLastActive":"2023-06-16T11:19:41+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-16T11:23:58+00:00","dateAnswered":"2023-06-15T23:55:36+00:00","acceptedAnswers":[{"commentID":380951,"body":"

Hi Mirelle,<\/p>


<\/p>

When applying the global update to modify the column, right under the selection of the column name, there are two checkboxes. One to change column property and the other is to change column cell data. Both are checked by default. To ensure only the new dropdown values are pushed and no existing values are updated, all you have to do it uncheck the box which says Change column cell data. This will remove the Pick value from list option altogether and you can apply the update.<\/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":[]}">

公式和函数趋势