引用另一张表- 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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!

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

 了解更多
@Pamela Wagner<\/a> There's nothing in core Smartsheet. <\/p>

If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.<\/p>

Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https:\/\/smartsheetguru.com\/contact<\/a><\/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":106845,"type":"question","name":"Count function","excerpt":"Hi I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong. =countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> \"N\/A\") The Test Script # column is the…","categoryID":322,"dateInserted":"2023-06-23T17:50:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":127983,"lastUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":46,"score":null,"hot":3375096049,"url":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106845,"commentID":382093,"name":"Re: Count function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382093#Comment_382093","dateInserted":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+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-23T19:30:46+00:00","dateAnswered":"2023-06-23T19:28:59+00:00","acceptedAnswers":[{"commentID":382092,"body":"

@Carol-Anne Cerbone<\/a> <\/p>

How about this?<\/p>

=COUNT([Test Script #]:[Test Script #]) - COUNTIF([Pass?]:[Pass?], =\"N\/A\")<\/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":106844,"type":"question","name":"Avg number of selections per condition","excerpt":"Hi there, I need some help. I'm trying to calculate the avg number of issues per condition in our master tracker. The issues are in a multi-value cell {Rev Rec Master Tracker Range 1} while the condition is a single-value cell in {Rev Rec Master Tracker Range 2} with values either as \"Backlog\" or \"Net New\". I'd like to…","categoryID":322,"dateInserted":"2023-06-23T17:36:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T18:36:10+00:00","insertUserID":162710,"insertUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162710,"lastUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":37,"score":null,"hot":3375089541,"url":"https:\/\/community.smartsheet.com\/discussion\/106844\/avg-number-of-selections-per-condition","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106844\/avg-number-of-selections-per-condition","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106844,"commentID":382062,"name":"Re: Avg number of selections per condition","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382062#Comment_382062","dateInserted":"2023-06-23T18:36:10+00:00","insertUserID":162710,"insertUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56: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-23T18:35:49+00:00","dateAnswered":"2023-06-23T18:26:57+00:00","acceptedAnswers":[{"commentID":382060,"body":"

So for \"Backlog\" you would want it to count 3 Action Plan Notes and then divide by 2 \"Backlog\" entries? If so, try this:<\/p>

=COUNTM(COLLECT({Action Plan Notes}, {RR Type}, @cell = \"Backlog\")) \/ COUNTIFS({RR Type}, @cell = \"Backlog\")<\/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"}]}],"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":[]}">

公式和函数趋势

  • 只复制某些列到一个新的工作表?

    我正试图将两张纸结合在一起，一张是用于购票的新纸，另一张纸已被另一个团队使用，该团队有一个人的门票信息，但也有他们团队的额外信息。我不希望团队在填写表格的同时还必须在输入表格中输入数据。我…

  • 计数功能

    你好，我需要你帮我弄个公式。我有一列，我想计数，但只基于一个值在不同的列。我试过下面的公式，但它不起作用，我不知道我做错了什么。=countif([Test Script #]:[Test Script #]， [Pass ?]:[Pass ?]， <> "N/A") Test Script #列是…

  • 每个条件的平均选择数

    你好，我需要一些帮助。我正在计算主跟踪器中每个条件的平均问题数。问题在多值单元{Rev Rec Master Tracker Range 1}中，而条件是{Rev Rec Master Tracker Range 2}中的单值单元，值为“Backlog”或“Net New”。我愿意......