索引公式取代VLookUp公式
你好
建议我将vlookup公式替换为Index(Collect)公式。这是我第一次体验索引和/或收集匹配概念，老实说，我在第一次尝试时很挣扎。我正试图从我的预算表到我的投资组合表拉总资金批准值。
这两个表格都以列标题ops#开始-这将是匹配的数据值，我想从列/单元格匹配“批准的总资金”中提取值。两张纸上的名字是一样的。
(尽我所能)在运营项目日志的标题为“已批准资金总额”的栏目中构建公式的英文声明。在名为“OPS#”的资金审批登录栏中，从运营项目日志中找到匹配的OPS#。如果找到匹配，则将资金批准日志中标题为“已批准资金总额”的列中的值拉到运营项目日志中。
在学习曲线上寻求一点帮助。
谢谢你！
黎明
最好的答案
-
DW1 ✭✭✭✭
只有在需要使用算术函数作为公式的一部分时才需要Collect()。
如果你只需要查找单个值，你可以使用这样的代码:
=INDEX(OPS#:Total Funds Approved，(MATCH([OPS#]1,OPS#:OPS#，0)，2)
这假定核准的资金总额列在OPS#列的右侧一行。
-
DW1 ✭✭✭✭
公式的最后一部分是列索引引用。因此，如果您的范围是21列，并且利息值在第21列(您的Total Funds Approved值)中，则最后一个数字需要是21。
=INDEX(ops#:Total Funds Approved，(MATCH([ops#]1, ops#: ops#，0)，21）
答案
-
DW1 ✭✭✭✭
只有在需要使用算术函数作为公式的一部分时才需要Collect()。
如果你只需要查找单个值，你可以使用这样的代码:
=INDEX(OPS#:Total Funds Approved，(MATCH([OPS#]1,OPS#:OPS#，0)，2)
这假定核准的资金总额列在OPS#列的右侧一行。
-
DW1 ✭✭✭✭
公式的最后一部分是列索引引用。因此，如果您的范围是21列，并且利息值在第21列(您的Total Funds Approved值)中，则最后一个数字需要是21。
=INDEX(ops#:Total Funds Approved，(MATCH([ops#]1, ops#: ops#，0)，21）
-
黎明麦卡利斯特 ✭✭✭✭
因为你对我关于索引(匹配)公式的问题很有帮助。我需要更多的帮助。今天我的索引(匹配)公式出错了，我唯一能确定的变化是我使用了一套新的表格。当我创建公式时，它在公式的末尾添加了一个先前不存在的额外括号。
详情如下
表1 - OPS追踪器
表格2 -封盖申请日志。这是我创建索引(匹配)公式的工作表
目标是从OPS跟踪器上的Cap请求日志中找到OPS编号，并返回项目阶段。如果没有找到匹配，则留空。
OPS #是两张表上的第一列。项目阶段是行动追踪器的第26栏。两张表上的列名不互为镜像(Ops # vs Ops Number)
这是我写的公式。
=IFERROR(INDEX({2022 OPS项目轨道- Prj阶段索引}，MATCH([OPS编号]@row， {2022 OPS项目- OPS#}， 0,26)， ""))
结果= #不正确的参数集
帮助吗?
帮助文章参考资料欧宝体育app官方888
类别
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-13T11:57:09+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":51,"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-13T11:57:09+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":"
