引用另一张表- VLOOKUP vs INDEX vs ??

贝茜科赫
贝茜科赫 ✭
在公式和函数

你好,

我需要用另一个工作表中的数字填充一个单元格，该数字基于在两个工作表中的不同列中找到的独立UID。不知道该往哪个方向走

对于每一行生成的行，D需要查看C.，找到/匹配A.的数据，然后在以下情况下用B填充D:

A.生ob欧宝娱乐app手机下载产单1/第一列:SKU标签号(UID)

B.生ob欧宝娱乐app手机下载产单1/第二栏:开工数量(操作员手动输入)

C.包装单2/第一列:SKU标签号(UID)

D.包装单2/第二栏:起始数量(公式栏)

标签:
· 在Facebook上分享 在Twitter上分享

最好的答案

  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    嗨@Bessie科赫

    听起来像INDEX()匹配会为你工作的!


    例如，你可以把这个放在第二页第二栏:

    =INDEX({Sheet 1列2}，MATCH([Column 1]@row， {Sheet 1列1}，0))


    第一个{Range}是包含要从另一个工作表调回的数据的列。然后在MATCH函数中，首先列出当前工作表中的值，然后在另一个工作表的第二个{Range}中搜索它。

    欢呼,

    吉纳维芙

    · 在Facebook上分享 在Twitter上分享
  • 贝茜科赫
    贝茜科赫 ✭
    ✓回答

    效果非常好!非常感谢!

    接下来的问题……是否有一种方法来嵌入一个IF/THEN索引/匹配?我在一些上面写着“NO MATCH”，然后意识到有两张纸在一张纸上。


    A.生ob欧宝娱乐app手机下载产单1/第一列:SKU标签号(UID)

    B.生ob欧宝娱乐app手机下载产单1/第二栏:结束数量(操作员手动输入)

    C.生ob欧宝娱乐app手机下载产单2/第一列:SKU标签号(UID)

    D.生ob欧宝娱乐app手机下载产单2/第二栏:结束数量(操作员手动输入)

    E.包装单3/栏1:水果名称

    F.包装单3/第二列:SKU标签号(UID)

    G.包装单3/第3栏:起始数量(公式栏)


    如果E =“Bananas”，那么F需要在a中找到匹配项，并用B填充G

    如果E =“苹果”，那么F需要在C中找到匹配项，并用D填充G

    · 在Facebook上分享 在Twitter上分享

答案

  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    嗨@Bessie科赫

    听起来像INDEX()匹配会为你工作的!


    例如，你可以把这个放在第二页第二栏:

    =INDEX({Sheet 1列2}，MATCH([Column 1]@row， {Sheet 1列1}，0))


    第一个{Range}是包含要从另一个工作表调回的数据的列。然后在MATCH函数中，首先列出当前工作表中的值，然后在另一个工作表的第二个{Range}中搜索它。

    欢呼,

    吉纳维芙

    · 在Facebook上分享 在Twitter上分享
  • 贝茜科赫
    贝茜科赫 ✭
    ✓回答

    效果非常好!非常感谢!

    接下来的问题……是否有一种方法来嵌入一个IF/THEN索引/匹配?我在一些上面写着“NO MATCH”，然后意识到有两张纸在一张纸上。


    A.生ob欧宝娱乐app手机下载产单1/第一列:SKU标签号(UID)

    B.生ob欧宝娱乐app手机下载产单1/第二栏:结束数量(操作员手动输入)

    C.生ob欧宝娱乐app手机下载产单2/第一列:SKU标签号(UID)

    D.生ob欧宝娱乐app手机下载产单2/第二栏:结束数量(操作员手动输入)

    E.包装单3/栏1:水果名称

    F.包装单3/第二列:SKU标签号(UID)

    G.包装单3/第3栏:起始数量(公式栏)


    如果E =“Bananas”，那么F需要在a中找到匹配项，并用B填充G

    如果E =“苹果”，那么F需要在C中找到匹配项，并用D填充G

    · 在Facebook上分享 在Twitter上分享
  • 吉纳维芙P。
    吉纳维芙P。 员工管理

    嗨@Bessie科赫

    是的!

    我们可以使用IFERROR函数来表示“如果有错误，请查看表2”。

    = IFERROR (第一个公式，第二个公式）

    或者:

    =IFERROR(INDEX({Sheet 1 Column 2}， MATCH([Column 1]@row， {Sheet 1 Column 1}， 0))， INDEX({第2页第2栏}= = = = = = = = = ={表格2第一栏}, 0)))


    看到的:IFERROR函数

    欢呼,

    吉纳维芙

    · 在Facebook上分享 在Twitter上分享
  • 贝茜科赫
    贝茜科赫 ✭

    非常感谢!!正在测试这个:)

    · 在Facebook上分享 在Twitter上分享
  • 贝茜科赫
    贝茜科赫 ✭

    测试了这个，它工作得很好!非常感谢!!

    · 在Facebook上分享 在Twitter上分享

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
Instead of comparing to the count of children, you would compare to the count of children that are blank or not blank.<\/p>


<\/p>

Change<\/p>

COUNT(CHILDREN())<\/p>

to<\/p>

COUNTIFS(CHILDREN(), OR(@cell <> \"//www.santa-greenland.com/community/discussion/comment/327636/\", @cell = \"//www.santa-greenland.com/community/discussion/comment/327636/\"))<\/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":106335,"type":"question","name":"Match Learner by Location in a Training Tracker for Metric Sheet","excerpt":"I am missing something, but I think it is because I am not accustom to working with dates. I am creating a metric sheet for dashboard. I have a training tracker that populates a date by a learners name & demographics (row) with columns of different courses (date columns). I didn't think the formula would be difficult , but…","categoryID":322,"dateInserted":"2023-06-12T19:39:08+00:00","dateUpdated":null,"dateLastComment":"2023-06-12T20:03:20+00:00","insertUserID":150616,"insertUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":150616,"lastUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3373200148,"url":"https:\/\/community.smartsheet.com\/discussion\/106335\/match-learner-by-location-in-a-training-tracker-for-metric-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106335\/match-learner-by-location-in-a-training-tracker-for-metric-sheet","format":"Rich","lastPost":{"discussionID":106335,"commentID":380182,"name":"Re: Match Learner by Location in a Training Tracker for Metric Sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380182#Comment_380182","dateInserted":"2023-06-12T20:03:20+00:00","insertUserID":150616,"insertUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+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-12T20:23:36+00:00","dateAnswered":"2023-06-12T19:52:03+00:00","acceptedAnswers":[{"commentID":380176,"body":"

=COUNTIFS({Learner Location Range}, [Location]@row, {Course Completion Date}, ISDATE(@cell))<\/p>

If I understand your scenario correctly, this may work.<\/p>"},{"commentID":380182,"body":"

Awesome, <\/p>

Exactly it! I was not identifying the date@cell. <\/p>

Thank you so very much!<\/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":106304,"type":"question","name":"I need help writing a formula.","excerpt":"Logic if cell b >0, then calculate the Sum Cell b-cell a","categoryID":322,"dateInserted":"2023-06-11T00:07:43+00:00","dateUpdated":"2023-06-12T08:42:14+00:00","dateLastComment":"2023-06-12T14:32:38+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-12T14:38:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":142964,"lastUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-06-12T18:47:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":67,"score":null,"hot":3373027821,"url":"https:\/\/community.smartsheet.com\/discussion\/106304\/i-need-help-writing-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106304\/i-need-help-writing-a-formula","format":"Rich","lastPost":{"discussionID":106304,"commentID":380099,"name":"Re: I need help writing a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380099#Comment_380099","dateInserted":"2023-06-12T14:32:38+00:00","insertUserID":142964,"insertUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-06-12T18:47:01+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-12T14:23:01+00:00","dateAnswered":"2023-06-12T14:18:50+00:00","acceptedAnswers":[{"commentID":380096,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/comment\/380093#Comment_380093\n <\/a>\n<\/div>\n

You would need to add another column to be able to make that calculation.<\/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":9971,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • Children()的真实计数，而不仅仅是非空格

    进行项目卷取，并尝试使用下面的公式来卷取完整的状态。=IF(COUNTIF(CHILDREN()， "Complete") = COUNT(CHILDREN())， "Complete"， "Not")然而，空白单元格对COUNT(CHILDREN())没有贡献，因此，如果有三个子单元格并且有人删除了3个单元格中的所有值或任何非…

  • 在训练跟踪器中根据位置匹配度量表的学习者

    我遗漏了一些东西，但我认为这是因为我不习惯与日期打交道。我正在为仪表板创建一个度量表。我有一个培训跟踪器，它根据学习者的姓名和人口统计数据(行)填充日期，其中包含不同课程的列(日期列)。我原以为配方不难，但是……

  • 我需要你帮我写一个公式。

    逻辑如果单元格b >0，则计算单元格b-单元格a的总和