公式-按等级索引/收集/匹配
好吧，我被难住了!
这是我的原始表格
这是我的目的地表
我想要的是……
专栏:承包商A
返回值来自:源表公司列
地点:
- 源工作表，类型列匹配目标工作表类别列
- Source Sheet Rating couln为最高值。
列:承包商B和承包商C
和上面一样，但是第二和第三高的值。
请帮忙，我完全把自己弄糊涂了。
谢谢。
最佳答案
-
保罗新来的 ✭✭✭✭✭✭要合并第一、第二和第三个最高评级，您可以使用@Genevieve P。然后加入
=INDEX(收集({公司列}，{类别}，(电子邮件保护)），{评级}，@cell = LARGE({评级}，1)）
只需根据需要将1更改为2或3。
thinkspi.com
答案
-
吉纳维芙P。 员工管理而不是INDEX(MATCH)，尝试使用INDEX(COLLECT)。
这将使用COLLECT函数来根据您的标准(类别)筛选源工作表，然后您可以使用行索引特性来确定您是在查找第一个匹配、第二个匹配还是第三个匹配。
试试这样做:
承包商的
=INDEX(收集({公司列}，{类别}，(电子邮件保护)), 1)
承包商B
要找到第二个匹配项，将末尾的1替换为2，如下所示:
=INDEX(收集({公司列}，{类别}，(电子邮件保护))，2）
承包商C
=INDEX(收集({公司列}，{类别}，(电子邮件保护))，3）
让我知道这是否有意义，是否适合你。
欢呼,
吉纳维芙
-
保罗新来的 ✭✭✭✭✭✭
要合并第一、第二和第三个最高评级，您可以使用@Genevieve P。然后加入
=INDEX(收集({公司列}，{类别}，(电子邮件保护)），{评级}，@cell = LARGE({评级}，1)）
只需根据需要将1更改为2或3。
thinkspi.com
帮助文章参考资料欧宝体育app官方888
类别
Start Date Time Helper<\/p>
=IFERROR(VALUE(YEAR(DATEONLY([Start Date]@row)) + \"//www.santa-greenland.com/community/discussion/85482/\" + IF(MONTH(DATEONLY([Start Date]@row)) < 10, \"0\") + MONTH(DATEONLY([Start Date]@row)) + IF(DAY(DATEONLY([Start Date]@row)) < 10, \"0\") + DAY(DATEONLY([Start Date]@row)) + IF([Start Time]@row < 1000, \"0\") + [Start Time]@row), \"//www.santa-greenland.com/community/discussion/85482/\")<\/p>
End Date Time Helper <\/p>
=IFERROR(VALUE(YEAR(DATEONLY([End Date]@row)) + \"//www.santa-greenland.com/community/discussion/85482/\" + IF(MONTH(DATEONLY([End Date]@row)) < 10, \"0\") + MONTH(DATEONLY([End Date]@row)) + IF(DAY(DATEONLY([End Date]@row)) < 10, \"0\") + DAY(DATEONLY([End Date]@row)) + IF([End Time]@row < 1000, \"0\") + [End Time]@row), \"//www.santa-greenland.com/community/discussion/85482/\")<\/p>
Formula to get your Yes\/No's<\/p>
=IF(COUNTIFS(SAP:SAP, SAP@row, [End Date Time Helper]:[End Date Time Helper], @cell >= [Start Date Time Helper]@row, [Start Date Time Helper]:[Start Date Time Helper], @cell <= [End Date Time Helper]@row) > 1, \"No\", \"Yes\")<\/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":106590,"type":"question","name":"Helper checkbox not working on reference sheet","excerpt":"I have multiple line items for a order in one sheet(Request Sheet) and in another sheet (Order sheet) where I have clubbed all the items in single row, based on the order ID from request sheet to order sheet I want to check the Quantity checkbox and update in the Order sheet, I tried index match formula but its not working…","categoryID":322,"dateInserted":"2023-06-19T11:07:26+00:00","dateUpdated":null,"dateLastComment":"2023-06-19T15:51:48+00:00","insertUserID":153826,"insertUser":{"userID":153826,"name":"Domnic Victor","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Domnic%20Victor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:46:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":153826,"lastUser":{"userID":153826,"name":"Domnic Victor","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Domnic%20Victor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:46:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":18,"score":null,"hot":3374363954,"url":"https:\/\/community.smartsheet.com\/discussion\/106590\/helper-checkbox-not-working-on-reference-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106590\/helper-checkbox-not-working-on-reference-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106590,"commentID":381176,"name":"Re: Helper checkbox not working on reference sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381176#Comment_381176","dateInserted":"2023-06-19T15:51:48+00:00","insertUserID":153826,"insertUser":{"userID":153826,"name":"Domnic Victor","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Domnic%20Victor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:46:33+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\/TUXJPHZT569J\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-19T16:01:16+00:00","dateAnswered":"2023-06-19T13:27:24+00:00","acceptedAnswers":[{"commentID":381141,"body":"
The issue is it doesn't know whether to check the box or not check the box because there are multiple ORD0241 on the Request sheet and one is checked and one is not checked. If I am understanding correctly you want the box checked if any of the ORD0241 are checked on the request sheet. If that is correct you would need to use a countifs formula with an If formula<\/p>
=IF(COUNTIFS({Order ID reference from request sheet},[Order ID]@row,{Quantity Helper reference from request sheet},1)>=1,1,0)<\/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":106584,"type":"question","name":"Index Match, how to recognize multiple values?","excerpt":"Hello, I have an issue with my index match formula. I want to locate the facility for a serial number. This works perfectly when there is only one value inside. However to reduce the amount of columns and options on the corresponding form, I changed it to one column that has multiple values. The formula is having…","categoryID":322,"dateInserted":"2023-06-18T21:21:35+00:00","dateUpdated":null,"dateLastComment":"2023-06-19T02:24:23+00:00","insertUserID":162488,"insertUser":{"userID":162488,"name":"Marcus Halvorson","title":"Marcus Halvorson","url":"https:\/\/community.smartsheet.com\/profile\/Marcus%20Halvorson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:46:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162488,"lastUser":{"userID":162488,"name":"Marcus Halvorson","title":"Marcus Halvorson","url":"https:\/\/community.smartsheet.com\/profile\/Marcus%20Halvorson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:46:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":30,"score":null,"hot":3374265958,"url":"https:\/\/community.smartsheet.com\/discussion\/106584\/index-match-how-to-recognize-multiple-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106584\/index-match-how-to-recognize-multiple-values","format":"Rich","lastPost":{"discussionID":106584,"commentID":381112,"name":"Re: Index Match, how to recognize multiple values?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381112#Comment_381112","dateInserted":"2023-06-19T02:24:23+00:00","insertUserID":162488,"insertUser":{"userID":162488,"name":"Marcus Halvorson","title":"Marcus Halvorson","url":"https:\/\/community.smartsheet.com\/profile\/Marcus%20Halvorson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:46:04+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\/NDLYM9DFIQOI\/f1-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"f1.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-19T02:22:26+00:00","dateAnswered":"2023-06-19T02:12:19+00:00","acceptedAnswers":[{"commentID":381111,"body":"
