☰

比较两张工作表的 3 列，并将第 4 列从工作表 1 拉到工作表 2 中匹配的位置

tag-icon • tag-icon microsoft-excel worksheet-function
比较两张工作表的 3 列，并将第 4 列从工作表 1 拉到工作表 2 中匹配的位置

我在office365中使用最新版本的excel。

我需要将我的数据（工作表 1）中的 3 列与查找表（工作表 2）中的 3 列进行比较，如果列匹配，我需要将工作表 2 中第 4 列的数据拉到工作表 1 中

有人能把这个函数发给我吗

一位非常沮丧的 Excel 菜鸟向你致以衷心的谢意 :)

答案1

诀窍是将三列集合中的数据合并起来，并添加一些内容以确保事物保持其“列的事实”关系。以下每个公式都适合您：

=IFERROR(XLOOKUP(A1:A8&"|"&B1:B8&"|"&C1:C8,Sheet2!A1:A8&"|"&Sheet2!B1:B8&"|"&Sheet2!C1:C8,Sheet2!D1:D8),"")

=IFERROR(INDEX(Sheet2!D1:D8,MATCH(A1:A8&"|"&B1:B8&"|"&C1:C8,Sheet2!A1:A8&"|"&Sheet2!B1:B8&"|"&Sheet2!C1:C8,0)),"")

=IFERROR(VLOOKUP(A1:A8&"|"&B1:B8&"|"&C1:C8,CHOOSE({1,2},Sheet2!A1:A8&"|"&Sheet2!B1:B8&"|"&Sheet2!C1:C8,Sheet2!D1:D8),2,FALSE),"")

=IF(A1:A8&"|"&B1:B8&"|"&C1:C8=Sheet2!A1:A8&"|"&Sheet2!B1:B8&"|"&Sheet2!C1:C8,Sheet2!D1:D8,"")

第一个使用现代的XLOOKUP()，第二个是标准INDEX/MATCH公式，第三个是用来实现的方法VLOOKUP()，第四个是简单的“这是否等于那？”IF()测试，如果为 TRUE 则返回一个值，如果为 FALSE 则不返回。

XLOOKUP()几乎总是用看起来更直接的公式完全取代INDEX/MATCH公式，这种公式更容易理解，尤其是对于新用户和接管他人电子表格的人来说。不是“总是”，而是“几乎总是”。我发现也许有 10% 的时间它不能很好地发挥作用，但其他人似乎从来没有用过它不起作用的地方。

这三个查找公式将对于不匹配的结果返回错误，因此每个公式都用函数包装，IFERROR()如果没有匹配则返回空白。

这四个公式中的每一个都获取每张工作表上的三列数据，并将它们组合在一起以获得要查找的单个值。有多种常见的连接方法：

  1. 从字面上理解，JOIN它们就像在这里完成的一样。使用&并集运算符将各部分组合在一起。
  2. 使用该CONCATENATE()功能。
  3. 使用该CONCAT()功能。
  4. 使用该TEXTJOIN()功能。

对于这种简单的事情，我只使用 #1。据说它比任何函数都快，所以当我第一次做电子表格时，它很不错。事情坚持下来了，是吗？CONCATENATE()没有更容易或更难，同样简单明了。CONCAT()并且TEXTJOIN()会在这里工作，最好是后者，除了……后者平面不会在SPILL列顶部的单个函数中工作，会自动为您填充。您必须复制并粘贴到列中。当出现新条目时，做更多这样的事情。如果删除了一行，处理有趣的事情。通常CONCAT()可以以某种方式工作SPILL，但这次需要聪明才智，我认为没有理由花时间。

四种公式中的方法都可以做到，SPILL所以一次性完成。

那么，如何维护列关系呢？这到底是什么意思，对吧？假设您要将两列放在一起进行查找。查找数据中的一行在第 1 列中有“person”，在第 2 列中有“al”。只需将它们组合起来即可得到“personal”——到目前为止还不错。在正在查看的数据中，一行在第 1 列中有“personal”，在第 2 列中有空白。只需将它们组合起来即可得到“personal”……Excel 会认为查找值已找到，这是匹配项！但事实并非如此。没有保留有关数据在两个来源中如何出现的信息来提供帮助。但是，如果您将第一列加上一个不常用的字符（我使用了该|字符）加上第二列，则将“personal|al”与“personal|”进行比较，Excel 会发现它们不匹配。该字符允许您组合这些值，以便您可以像查找单个值一样查找内容，但保留了它们的真实性质。

该VLOOKUP()公式使用了一个技巧，让您在公式中用组合的三列（虚拟表中的列“1”）和包含要获取的值的列（虚拟表中的列 2）创建一个虚拟表。它使用CHOOSE()需要两列的函数，组合的三列现在是其中一列，结果列是第二列，并且使用了一个技巧，让 Excel 为您提供两列，“选择”两列，而不是像您想象的那样，选择其中一列或另一列。技巧是您{1,2}看到的而不是或1（2单独使用，没有花括号）。花括号使其成为 Excel 所称的array constant，并且 Excel 将对其中的两个值起作用，因此您可以“花一分钱得到两个”。（它们可以是您需要的任意长。并且不必按顺序排列，或者只使用一次值： {3,1,5,5,5,8,22,27,1,2,3}如果您至少有 27 列可供选择，那就没问题了。这里您只需要两个。）

因此，Excel 在函数中创建一个虚拟的两列表格，然后您告诉它将第 2 列返回到 Sheet1。

还有很多其他方法可以做到这一点。大多数都是“OG”方法，因为 Excel 很难处理事情，而且涉及一些看起来很古怪的东西。一旦你做了几次，它们就很简单（有点）和直接（有点），但可以理解。它们只是一开始很难掌握的东西，它们会生成虚拟数组常量，这些常量的长度受 Excel 限制，因此如果三列中的每一列都有很多行和较长的数据，它们可能会失败。而且由于它们可能会在 6,000 或 30,000 行中失败，你可能不会立即注意到。做了很多工作，然后发现你必须重新开始。

VLOOKUP()和存在限制MATCH()（所以在INDEX/MATCH公式中）。Excel 限制只查看每个组合值的前 256 个字符。因此，如果三列中的值较长，则可能会遇到麻烦。前者有一个涉及虚拟数组常量的解决方法，因此限制与我提到的那些非常奇怪的方法相同，而新方法XMATCH()克服了MATCH()的限制。但是如果使用新函数，为什么不直接转到XLOOKUP()，对吧？不过要记住，当XLOOKUP()它不能满足您的需求时。

所以，你对此还不熟悉。我将提到另一个函数，该函数在许多此类情况下都很有用，但在这里不适用于你： FILTER()。

FILTER()会得到一个没有空格的列表。所以它在这里不起作用，因为它会将值放入没有匹配项的行中。它更像是“返回所有匹配项，但不必费心将返回项与匹配的数据进行匹配”。在这里非常不合适，但是当您需要一组数据并且空格实际上会成为问题时，它FILTER()非常方便。您应该尽快查看它以解决您可能遇到的问题。

相关内容