INDEX(MATCH)返回无效的列值

那鸿书
那鸿书 ✭✭
编辑12/09/19 在公式和函数

我有两个表，它们之间有一个共同的“Name”列。我希望第二个工作表中的复选框反映在第一个工作表中，考虑到不是所有的名称都将在第二个工作表中使用。

为此，我通常使用INDEX(MATCH)。MATCH()能够返回Name在另一个工作表中出现的行号(或缺少匹配的适当错误)，但是INDEX(MATCH())返回包含复选框的列的错误。我期望根据检查状态得到0或1的值。

INDEX引用指向另一个工作表上的复选框列。我也尝试过VLOOKUP，但它返回相同的#INVALID COLUMN VALUE错误。

Smartsheet是否提供了一种更好的方法来进行这种查找?

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

评论

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
Hi @Clare123<\/a> <\/p>

I hope you're well and safe!<\/p>

Yes, absolutely.<\/p>

Here's the structure.<\/p>

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))<\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":106314,"type":"question","name":"Flexible Duration value when calculating scheduling dates","excerpt":"I have a Smartsheet which is a project plan with the Dependencies and Duration columns is being used in scheduling calculations to automatically deriving the start\/end dates for each task. However, I need the Duration to be dynamic e.g. linking cell to a cell in another sheet or to contain a formulae.. However, I cannot…","categoryID":322,"dateInserted":"2023-06-12T12:39:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T11:22:45+00:00","insertUserID":162290,"insertUser":{"userID":162290,"name":"Mark Duddy","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Mark%20Duddy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-13T11:41:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-13T13:35:57+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":56,"score":null,"hot":3373230151,"url":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","format":"Rich","lastPost":{"discussionID":106314,"commentID":380264,"name":"Re: Flexible Duration value when calculating scheduling dates","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380264#Comment_380264","dateInserted":"2023-06-13T11:22:45+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-13T13:35:57+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-13T11:24:19+00:00","dateAnswered":"2023-06-13T11:22:45+00:00","acceptedAnswers":[{"commentID":380264,"body":"

Hi @Mark Duddy<\/a> <\/p>

If you're using dependencies in a Project Sheet, then the Duration column is not able to be updated via formulas <\/a>or cell links. This is because it's used in combination with the Start and Finish dates in your sheet to automatically generate data (e.g. if you add the Start date and a Duration, the Finish date populates. If you add the Start date and Finish date, the Duration automatically populates).<\/p>

You could set up your sheet to use regular columns and use your own formulas to generate automatic dates based on a text\/number \"duration\" column, but then these details would not be associated with Critical paths or other Project features in a Gantt chart.<\/p>

Cheers,<\/p>

Genevieve<\/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":106342,"type":"question","name":"I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula","excerpt":"I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula. Example of 2 IF formulas: =IF([Member 2 Registration Type]@row = \"Golf & Dinner - General Public\", \"135\") and =IF([Member 2 Registration Type]@row = \"Dinner Only\", \"60\") The IF formulas are working as they should and are…","categoryID":322,"dateInserted":"2023-06-12T21:19:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T02:23:40+00:00","insertUserID":162311,"insertUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162311,"lastUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3373228985,"url":"https:\/\/community.smartsheet.com\/discussion\/106342\/i-am-trying-to-use-sum-to-add-the-values-from-multiple-cells-in-that-were-populated-by-an-if-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106342\/i-am-trying-to-use-sum-to-add-the-values-from-multiple-cells-in-that-were-populated-by-an-if-formula","format":"Rich","lastPost":{"discussionID":106342,"commentID":380235,"name":"Re: I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380235#Comment_380235","dateInserted":"2023-06-13T02:23:40+00:00","insertUserID":162311,"insertUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+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-13T02:23:47+00:00","dateAnswered":"2023-06-13T02:23:40+00:00","acceptedAnswers":[{"commentID":380235,"body":"

Thanks! That worked!<\/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":9977,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 查找工作表B中的多个列值以匹配工作表A中的值以返回工作表B中的值

    你好，我试图使一个公式，计数从表a对表B中的多个列的值，如果只找到1个总匹配，然后查找值从表a对表B，并根据结果，从状态列拉值。如果有太多相同的结果或没有找到结果返回一个文本…

  • 计算调度日期时的弹性持续时间值

    我有一个智能表，这是一个项目计划的依赖关系和持续时间列正在调度计算中使用，以自动派生每个任务的开始/结束日期。然而，我需要持续时间是动态的，例如链接单元格到另一个工作表中的单元格或包含公式。然而，我不能……

  • 我试图使用SUM来添加由IF公式填充的多个单元格中的值

    我试图使用SUM来添加由IF公式填充的多个单元格中的值。2个IF公式的例子:=IF([成员2注册类型]@row =“高尔夫和晚餐-一般公众”，“135”)和=IF([成员2注册类型]@row =“仅限晚餐”，“60”)IF公式正在工作，因为他们应该是……