指数公式
我正试图从另一张表(表称为SOW BB28-A跟踪)的特定标准拉入日期。例如，我有一个项目(舰队号)和保修开始日期。我想根据车队编号将保修日期拉到主表中。我已经尝试了INDEX公式一百万种不同的方法，它不适合我。
=指数（{SOW BB28-A跟踪距离2}，MATCH([机队号]@row， {SOW BB28-A跟踪距离3}，0)
我知道这应该很简单。我错过了什么?
答案
-
JennS_ ✭✭
我在两张单独的纸上有一个船队号码。我想拉出特定车队编号的保修开始日期，并在另一张表中更新与之匹配的车队编号。见下面的母表。我正试图从下面的第二张图片中拉出保修日期，进入这张车队号码匹配的表格。
-
保罗H ✭✭✭✭✭✭可能只是末尾少了一个括号
=指数（{SOW BB28-A跟踪距离2}，MATCH([机队号]@row， {SOW BB28-A跟踪距离3}，0)）
-
JennS_ ✭✭
嗨，那对我没用。我真的想不明白!
-
吉纳维芙P。 员工管理你的公式语法是正确的-我想尝试排除一些不同的事情。
首先，检查以确保您的两个范围正在查看正确的列，并且选择了完整的列
- {SOW BB28-A跟踪范围2}=保修开始日期
- {SOW BB28-A range 3} =机队编号
如果这是正确的，接下来要检查的是公式是否在当前工作表中为您的舰队编号找到匹配。
=COUNTIF({SOW BB28-A range 3}，[舰队编号]@row)
这应该会给你一个数字。如果你得到0，那么公式没有找到这两列之间的匹配-在这种情况下，我们需要知道你的两个[Fleet No]列是什么类型的列，以及如何创建/输入数字。
最后要说的是……我看到你的公式是[舰队号]@row，但列名看起来是[舰队号]。带句号。请确保公式中的列名与当前工作表中的列名完全相同
如果这没有帮助，让我们知道如果你看到一个公式错误的消息(和哪一个)，或者如果你得到一个不正确的结果!
谢谢,
吉纳维芙
-
JennS_ ✭✭
吉纳维芙-谢谢你的笔记。我把这些项目都按下面的公式进行了核对:
=INDEX({SOW BB28-A保修开始日期}，MATCH([机队编号]@row)， {SOW BB28-A跟踪机队编号}，0))
我得到的错误是# unseable
我甚至试过:
=INDEX({SOW BB28-A保修开始日期}，COUNTIF([机队编号]@row)， {SOW BB28-A跟踪机队编号}，0))
还是不行。问题是，你是被限制在一张表格里做这种公式吗?例如，我有另一个列，它也对另一个表进行索引。这会引起问题吗?我也在主工作表中使用了链接单元格，但是我有很多链接单元格，我试图通过索引公式更容易地提取信息。任何额外的帮助都是感激的。
-
吉纳维芙P。 员工管理
对于你的第一个公式，在MATCH(___)之后有一个额外的)应该被删除:
=INDEX({SOW BB28-A保修开始日期})，MATCH([机号,{所以]@rowwbb28 - a跟踪船队编号}，0))
对于COUNTIF公式，我打算完全单独测试它，只是为了看看它是否找到了匹配!这是一个故障排除步骤，而不是您的最终公式
=COUNTIF({SOW BB28-A range 3}，[舰队编号]@row)
但在此之前，看看删除多余的)是否能使第一个公式工作!即使在另一个列中使用INDEX(MATCH)，也应该能够在这个列中使用它。只要确保你没有复制/粘贴相同的公式和编辑引用因为这将更新整个工作表的引用。相反，应该从头开始创建新的引用，以确保它正在查看正确的列。
如果有帮助，请告诉我!
-
JennS_ ✭✭
-
吉纳维芙P。 员工管理
COUNTIF能起作用真是太好了!:)这意味着它应该找到一个匹配。
#无效的列值通常意味着列类型不同于你放入其中的值。你能否证实这两个列是日期类型的列吗?
{SOW BB28-A保修开始日期}和正在输入公式的列?
-
JennS_ ✭✭
我刚确认过，它们不都是日期列!所以我更新了它，它工作了!真不敢相信这么简单的事。谢谢你的帮助!!你是最棒的。
-
吉纳维芙P。 员工管理
完全没问题!我很高兴我们最终做到了
帮助文章参考资料欧宝体育app官方888
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":44,"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":"
