索引匹配，如何识别多个值?

马库斯Halvorson
马库斯Halvorson ✭
在公式和函数

你好,

我的索引匹配公式有一个问题。我要找到那个设施的序列号。当内部只有一个值时，这可以完美地工作。但是，为了减少相应表单上的列和选项的数量，我将其更改为具有多个值的一列。公式很难看出该列中存在序列号，因为单元格中有两个。我怎么解决这个问题?


下面的工作公式(当它返回到库存时，它为正，表示它回到了名为“pes”的家庭仓库)

f1.JPG


=如果((电子邮件保护)> 0， "PES"， INDEX(Facility:Facility, MATCH(6633112443，[显微镜:1]:[显微镜:1]，0))


但是，当我更改列以允许在一个单元格中包含多个值时，它不再识别序列号。


任何帮助都是感激的!

谢谢,

· 在Facebook上分享 在Twitter上分享

最佳答案

答案

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

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!

进入赢取免费会议通行证到ENGAGE 2023。

 了解更多
TODAY(), Fleet:Fleet, \"A220\")","categoryID":322,"dateInserted":"2023-06-21T16:14:49+00:00","dateUpdated":null,"dateLastComment":"2023-06-21T20:20:41+00:00","insertUserID":162138,"insertUser":{"userID":162138,"name":"Louis.Smith","url":"https:\/\/community.smartsheet.com\/profile\/Louis.Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T20:28:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":162138,"lastUser":{"userID":162138,"name":"Louis.Smith","url":"https:\/\/community.smartsheet.com\/profile\/Louis.Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T20:28:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":38,"score":null,"hot":3374745330,"url":"https:\/\/community.smartsheet.com\/discussion\/106707\/how-to-count-how-many-interviews-in-the-current-week","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106707\/how-to-count-how-many-interviews-in-the-current-week","format":"Rich","lastPost":{"discussionID":106707,"commentID":381628,"name":"Re: How to count how many interviews in the current week?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381628#Comment_381628","dateInserted":"2023-06-21T20:20:41+00:00","insertUserID":162138,"insertUser":{"userID":162138,"name":"Louis.Smith","url":"https:\/\/community.smartsheet.com\/profile\/Louis.Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T20:28:23+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\/TNZRUD6K3A2N\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-21T20:21:04+00:00","dateAnswered":"2023-06-21T19:39:48+00:00","acceptedAnswers":[{"commentID":381601,"body":"

@Louis.Smith<\/a>,<\/p>

Awesome, glad it worked! Please remember to click the Answer button if you don't mind. <\/p>

Re: \"Is there a way to create a formula for the current?\" - By this do you mean for the current week?<\/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":106700,"type":"question","name":"If Or Formula Help","excerpt":"My formula is working in Excel, but not in Smartsheet. I am still fairly new to smartsheet so any help would be appreciated. Basically its looking at the GLAcct column and find certain values and creating key terms for those values. Ex: If GLAcct = 710111 or 710121, then show Key Salary.","categoryID":322,"dateInserted":"2023-06-21T15:12:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-21T17:02:32+00:00","insertUserID":162062,"insertUser":{"userID":162062,"name":"mragans23","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/mragans23","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T18:21:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162062,"lastUser":{"userID":162062,"name":"mragans23","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/mragans23","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T18:21:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3374728518,"url":"https:\/\/community.smartsheet.com\/discussion\/106700\/if-or-formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106700\/if-or-formula-help","format":"Rich","lastPost":{"discussionID":106700,"commentID":381545,"name":"Re: If Or Formula Help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381545#Comment_381545","dateInserted":"2023-06-21T17:02:32+00:00","insertUserID":162062,"insertUser":{"userID":162062,"name":"mragans23","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/mragans23","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T18:21:45+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\/WU7DXUC1MGJ2\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-21T18:54:42+00:00","dateAnswered":"2023-06-21T15:29:14+00:00","acceptedAnswers":[{"commentID":381516,"body":"

Hi @mragans23<\/a>,<\/p>

In Smartsheet you'd need to add @row to the GLAcct portion of your the formula. <\/p>

For example, the first section would be:<\/p>

=IF(OR(GLAcct@row = \"710111\", GLAcct@row = \"710121\"), \"Key Salary\")<\/p>

Once you've entered it for one row, you can then right click the cell and \"Convert to Column Formula\" to have it applied to all rows in the column. <\/p>

Other than that, I think everything should be OK once you add the @row in.<\/p>

Hope that helps; any questions etc. then just post! 😊<\/span><\/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":106682,"type":"question","name":"Make sum of one column in function of value of another","excerpt":"Hello everybody, I did not find my answer in the different discussions, then I hope you will be able to help me. I have a smart sheet with 2 columns: Part number Quantity I would like to count the total of parts with a certain number. Finally, it is an addition of the quantity when the P\/N is equal to a certain value. I…","categoryID":322,"dateInserted":"2023-06-21T06:31:14+00:00","dateUpdated":"2023-06-21T07:41:41+00:00","dateLastComment":"2023-06-21T11:14:19+00:00","insertUserID":159154,"insertUser":{"userID":159154,"name":"Guillaume B.","url":"https:\/\/community.smartsheet.com\/profile\/Guillaume%20B.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/S5NT3AR3967R\/nA4FRK2S3XJG9.jpg","dateLastActive":"2023-06-21T13:09:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":159154,"lastUserID":159154,"lastUser":{"userID":159154,"name":"Guillaume B.","url":"https:\/\/community.smartsheet.com\/profile\/Guillaume%20B.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/S5NT3AR3967R\/nA4FRK2S3XJG9.jpg","dateLastActive":"2023-06-21T13:09:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":34,"score":null,"hot":3374676333,"url":"https:\/\/community.smartsheet.com\/discussion\/106682\/make-sum-of-one-column-in-function-of-value-of-another","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106682\/make-sum-of-one-column-in-function-of-value-of-another","format":"Rich","lastPost":{"discussionID":106682,"commentID":381436,"name":"Re: Make sum of one column in function of value of another","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381436#Comment_381436","dateInserted":"2023-06-21T11:14:19+00:00","insertUserID":159154,"insertUser":{"userID":159154,"name":"Guillaume B.","url":"https:\/\/community.smartsheet.com\/profile\/Guillaume%20B.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/S5NT3AR3967R\/nA4FRK2S3XJG9.jpg","dateLastActive":"2023-06-21T13:09:56+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-21T11:14:29+00:00","dateAnswered":"2023-06-21T11:14:19+00:00","acceptedAnswers":[{"commentID":381436,"body":"

I finally found the correct formula:<\/p>

=SUMIF([Part number]$185:[Part number]$369, CONTAINS([Part number]167, @cell), Quantity$185:Quantity$369)<\/code><\/strong><\/p>

Like that it is working partially, because as I have sub assemblies, I will need to take the quantity of the PARENT() and multiply it to have the correct value.<\/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":[]}">

公式和函数趋势

  • 如何计算本周面试的次数?

    我目前正在尝试跟踪每个机队类型在本周安排了多少次面试?我的公式是从今天算起的7天，但不包括今天。=COUNTIFS([面试日期]:[面试日期]，@cell =今天(7)，[面试日期]:[面试日期]，@cell >今天()，舰队:舰队，“A220”)

  • 如果或公式帮助

    我的公式在Excel中工作，但在Smartsheet中不行。我仍然是相当新的智能表，所以任何帮助将不胜感激。它查看GLAcct列，找到某些值并为这些值创建关键字。例如:如果GLAcct = 710111或710121，则显示Key Salary。

  • 求一列值与另一列值的和

    大家好，我在不同的讨论中没有找到我的答案，希望大家能够帮助我。我有一个智能表，有两列:零件编号数量我想计算零件的总数与一定的数字。最后，当P/N等于某一值时，它是数量的加法。我…