VLOOKUP对另一个VLOOKUP的if为false

沃尔特Kerfoot
沃尔特Kerfoot ✭✭
在公式和函数

我有一个工作表，我想引用包含工作人员的5个参考表。因为工作人员的规模很大。我们把它们按地区分开。我想创建一个列公式，它将查找“地区ID”并返回“用户”，如果它们存在于5个参考表之一。这是我现在的公式:

=IF([District ID]@row = [District ID]@row, VLOOKUP([District ID]@row， {West}， 2，假)，VLOOKUP([District ID]@row， {District}， 2，假)，VLOOKUP([District ID]@row， {East}， 2，假)，VLOOKUP([District ID]@row， {South}， 2，假)，VLOOKUP([District ID]@row， {South}， 2，假)，VLOOKUP([District ID]@row， {North}， 2，假))

当我使用这个公式时，它给我一个#NO MATCH。如果我将我在District或其他任何地方寻找的值放入公式中。所以我认为它只执行第一次vlookup而不做第二次，第三次，第四次或第五次如果它在第一次中没有找到匹配。

在过去，我不再使用VLOOKUP，而是使用INDEX SUMIF HAS来提取符合条件的特定联系人信息。我不确定我是否可以使这个工作为这个用例。这是我要用的:

=INDEX([Reference sheet column I want in}， SUMIFS({Reference sheet Row ID}， {Reference sheet column Criteria}， HAS(@cell， "Reference Criteria"@row))， 0)


任何建议或公式重写都很感激!

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

最佳答案

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

    嗨@Walter Kerfoot

    而不是使用IF，尝试使用IFERROR。当其中一个VLOOKUp没有找到匹配，它将返回一个#NoMatch错误，所以我们可以说如果有一个错误，移动到下一个VLOOKUp…如果一个错误(另一个IFERROR)，移动到下一个VLOOKUP，等等。

    =IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP([District ID]@row， {West}， 2, false)， VLOOKUP([District ID]@row， {District}， 2, false))， VLOOKUP([District ID]@row， {East}， 2, false))， VLOOKUP([District ID]@row， {South}， 2, false))， VLOOKUP([District ID]@row， {South}， 2, false))， VLOOKUP([District ID]@row， {North}， 2, false))

    看到的:IFERROR函数

    欢呼,

    吉纳维芙

    · 在Facebook上分享 在Twitter上分享

答案

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

    嗨@Walter Kerfoot

    而不是使用IF，尝试使用IFERROR。当其中一个VLOOKUp没有找到匹配，它将返回一个#NoMatch错误，所以我们可以说如果有一个错误，移动到下一个VLOOKUp…如果一个错误(另一个IFERROR)，移动到下一个VLOOKUP，等等。

    =IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP([District ID]@row， {West}， 2, false)， VLOOKUP([District ID]@row， {District}， 2, false))， VLOOKUP([District ID]@row， {East}， 2, false))， VLOOKUP([District ID]@row， {South}， 2, false))， VLOOKUP([District ID]@row， {South}， 2, false))， VLOOKUP([District ID]@row， {North}， 2, false))

    看到的:IFERROR函数

    欢呼,

    吉纳维芙

    · 在Facebook上分享 在Twitter上分享

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
It would look something like this:<\/p>

=COUNTIFS({状態 Status}, OR(@cell = \"未着手 Not Started\", @cell = \"進行中 In Progress\", @cell = \"現場確認 Field Review\", @cell = \"保留中（予算）On Hold (Funding)\", @cell = \"保留中 On Hold\"), {エリア Area}, @cell = \"Example1\")<\/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":105974,"type":"question","name":"Help creating a formula that generates the current fiscal year","excerpt":"The following formula is #UNPARSEABLE. I'm not sure why. Please help. =IF(MONTH(TODAY())>=7, YEAR(TODAY())&\"-\"&(YEAR(TODAY())+1), (YEAR(TODAY())-1)&\"-\"&YEAR(TODAY()))","categoryID":322,"dateInserted":"2023-06-02T19:06:56+00:00","dateUpdated":null,"dateLastComment":"2023-06-02T19:46:15+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161705,"lastUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3371469191,"url":"https:\/\/community.smartsheet.com\/discussion\/105974\/help-creating-a-formula-that-generates-the-current-fiscal-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105974\/help-creating-a-formula-that-generates-the-current-fiscal-year","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":105974,"commentID":378672,"name":"Re: Help creating a formula that generates the current fiscal year","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/378672#Comment_378672","dateInserted":"2023-06-02T19:46:15+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+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-02T19:45:43+00:00","dateAnswered":"2023-06-02T19:21:45+00:00","acceptedAnswers":[{"commentID":378667,"body":"

The concatenate operator in Smartsheet is + instead of &.<\/p>


<\/p>

=IF(MONTH(TODAY())>=7, YEAR(TODAY())+\"-\"+(YEAR(TODAY())+1), (YEAR(TODAY())-1)+\"-\"+YEAR(TODAY()))<\/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":105970,"type":"question","name":"Trying to evaluate multiple columns with an IF statement","excerpt":"I keep getting a syntax error for some reason but can't figure out why. I am basically trying to set a status icon of 'Sunny\" if the status of the risk is \"closed\" and if not closed, i am using the other icons based on the level of the risk. here is the formula: =IF([Status]@row = “Closed”, “sunny”, IF([Risk Score]@row <=…","categoryID":322,"dateInserted":"2023-06-02T17:58:00+00:00","dateUpdated":null,"dateLastComment":"2023-06-02T19:19:11+00:00","insertUserID":162035,"insertUser":{"userID":162035,"name":"Drew N.","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Drew%20N.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:20:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-02T20:54:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3371464031,"url":"https:\/\/community.smartsheet.com\/discussion\/105970\/trying-to-evaluate-multiple-columns-with-an-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105970\/trying-to-evaluate-multiple-columns-with-an-if-statement","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":105970,"commentID":378663,"name":"Re: Trying to evaluate multiple columns with an IF statement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/378663#Comment_378663","dateInserted":"2023-06-02T19:19:11+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-02T20:54:24+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-02T18:44:44+00:00","dateAnswered":"2023-06-02T18:24:38+00:00","acceptedAnswers":[{"commentID":378641,"body":"

I think you are overcomplicating it. Try this instead:<\/p>

=IF([Status]@row = \"Closed\", \"sunny\", IF([Risk Score]@row <= 25, \"partly sunny\", IF([Risk Score]@row <= 50, \"cloudy\", IF([Risk Score]@row <= 90, \"rainy\", \"stormy\"))))<\/p>


<\/p>

It also looks like some of your quotes are slanted and others are straight up and down. The slanted ones are called \"Smart Quotes\" which (ironically enough) are not recognized as valid characters in Smartsheet formulas. You will need to either retype them here in the Community (which I did above already), directly in Smartsheet, or in a text editor such as Notepad (not Word).<\/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"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 具有来自多个列的多个条件的COUNTIFS

    嗨，我已经尝试了各种不同的公式从不同的帖子在这里，但仍然保持不可解析。我正在尝试创建一个公式，从另一个工作表链接，以找到以下标准:如果行包含以下任何值:“未启动”，“正在进行中”，“实地审查”，“等待中”…

  • 帮助创建一个产生当前财政年度的公式

    下面的公式是#UNPARSEABLE。我不知道为什么。请帮助。=如果(月(今天())> = 7年(今天())&”——“&((今天())+ 1年),(年今天())(1)&”——“一年(今天()))

  • 试图用IF语句计算多个列

    我一直得到一个语法错误的原因，但不知道为什么。如果风险的状态是“关闭”，我基本上试图设置一个状态图标为“晴朗”，如果没有关闭，我使用基于风险水平的其他图标。公式如下:=IF([状态]@row =“关闭”，“晴朗”，IF([风险评分]@row <=…