如果函数
我只想返回Completed或Not Completed的值。
如果列"Daily" = "#NO MATCH"返回not completed,否则返回completed
我使用
然而,当每日专栏是“#NO MATCH”时,我没有得到“未完成”。
非常感谢
答案
-
Sue08 ✭
嗨,基督教
多谢。
我想做的是确定哪个国家还没有完成智能表格调查。答复见“国内流离失所者国家程序审查调查”页。在我的计算表上,我列出了我希望完成调查的所有国家。我使用vlookup来检查调查表上列出的国家与我的计算表上的国家列表。我期望的结果是有一列“完成”和“未完成”针对我在计算表上列出的每个国家。
我希望这是有意义的
表格1 -表格调查结果
表格2 -计算表
-
凯利摩尔 ✭✭✭✭✭✭
@Christian伯爵的方法将工作-你甚至可以改变基督教的IFERROR稍微取代“”给你“No Match”,你的IF语句可以调整找到。话虽如此,试着调整你当前的IF语句:
=如果(返回错误((电子邮件保护))、“未完成”、“已完成”)
这将发现错误。
凯利
-
Sue08 ✭
嗨
我也不能让这个工作。IFERROR语句我不确定
它返回所有国家的“完整”,而不识别不在编辑参考表上的国家
-
凯利摩尔 ✭✭✭✭✭✭
嘿,苏
让我们回顾一下。查看您的屏幕截图,似乎您有其他列/字段依赖于此数据-这将导致错误级联到这些单元格。计算函数通常不会在计算范围内出现错误时执行。由于这些其他字段似乎需要Daily结果,我们必须使用Christian的IFERROR方法来清除错误。
当您使用IFERROR公式时,您将整个原始公式,括号和所有内容包装在IFERROR()函数中。在插入整个公式之后,您可以添加一个逗号,然后在发生错误时添加您希望发生的任何情况。Christian的公式是用来强制错误的单元格显示一个空白单元格。你可以强迫细胞做任何你喜欢的事。为了使插入文本的位置更明显,我将显示带有“No Match”的公式。为了清楚起见,此公式将放在Daily列中,并替换原来的VLOOKUP公式。
=IFERROR (VLOOKUP([Primary Column]@row,{哪个国家},1,false),“不匹配”)
这就是说,执行VLOOKUP公式,但如果发生任何错误,则插入“No Match”。同样,您可以在引号之间插入任何内容-或者如果您只是使用双引号,其中没有文本,那么将插入一个空白单元格。
你应该能够做你的IF语句现在在你的[完成/不完成]列-无论你插入的文本现在是可搜索的。
凯利
-
Sue08 ✭
好的,明白了
谢谢
帮助文章参考资料欧宝体育app官方888
类别
Try this<\/p>
=IF(Available@row > 0, \"PES\", JOIN(INDEX(COLLECT(Facility:Facility, [Microscope: 1]:[Microscope: 1], HAS(@cell,\"6633112443\")), 1)))<\/p>
Will this work for you?<\/p>
Kelly<\/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":106579,"type":"question","name":"Split Text","excerpt":"Can someone assist me? I need a formula on how to split the text for this below. Ex. LOW - VS-87 - Service-FS Battery Replacement Lead-MEGA MALL-1259410 I need a to have a column that would write MEGA MALL only.","categoryID":322,"dateInserted":"2023-06-17T17:04:55+00:00","dateUpdated":null,"dateLastComment":"2023-06-18T01:39:56+00:00","insertUserID":103391,"insertUser":{"userID":103391,"name":"Marilen.Navarro103391","url":"https:\/\/community.smartsheet.com\/profile\/Marilen.Navarro103391","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-09\/60\/88\/n6088a8f5166f388fd95cfcb413bcbd0e.jpg","dateLastActive":"2023-06-18T01:41:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-06-19T02:46:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3374075691,"url":"https:\/\/community.smartsheet.com\/discussion\/106579\/split-text","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106579\/split-text","format":"Rich","lastPost":{"discussionID":106579,"commentID":381096,"name":"Re: Split Text","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381096#Comment_381096","dateInserted":"2023-06-18T01:39:56+00:00","insertUserID":112221,"insertUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-06-19T02:46:11+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-18T01:42:10+00:00","dateAnswered":"2023-06-18T01:39:56+00:00","acceptedAnswers":[{"commentID":381096,"body":"