☰

如果第一列与另一张工作表上的值匹配，如何返回一行中的所有单元格

tag-icon • tag-icon microsoft-excel worksheet-function search vlookup
如果第一列与另一张工作表上的值匹配，如何返回一行中的所有单元格

我在 Excel 工作簿中有 2 个工作表选项卡，tickets并且tickets info。我的 A 列中有值tickets（来自 A2:A500）。我想搜索ticket工作表的 A2 到 A500 中的每个值，并在 的 A 列中查找tickets_info。如果有匹配项，则返回该行的所有单元格（ticket_info包含从 A 列到 N 列的每个票证的信息）。

我使用以下公式：

=VLOOKUP(A2,tickets_info!A$2:N$5000,2,FALSE)

将其写入ticket表格中的 C 列（A 列包含票号，B 列包含用户名）。

我使用了 A2 到 N5000，这是工作表中数据的范围ticket_info，其中包含有关每张票的许多列信息。票号位于 A2 到 A5000 的 A 列中。

当我提交公式时，遇到“无效引用错误”。

答案1

好的，我想我明白你想做什么了，我想VLOOKUP 不能是实现这一目标的方法，MATCH而是使用。 让我们开始...

第一种方法是“所有值都在一个单元格中”：

  • 在“票”表上

    1. C2细胞放公式=IFERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),0)；

    2. D2细胞放公式=IF($C2>0,INDIRECT("'tickets_info'!O"&$C2),"")；

    3. 选择 C2 和 D2 单元格并将公式复制到第 500 行。

    4. （可选）隐藏 C 列。

  • 在工作表“tickets_info”上

    1. 在O2电池上放公式=B2 & " " & C2 & " " & ... & N2；

    2. 将公式复制到第 5000 行。

    3. （可选）隐藏 O 列。

编辑：如果由于某种原因，您想要/需要保持“tickets_info”表不变，该怎么办？

您可以使用工作表“tickets”D2 单元格中经过部分修改的公式版本来实现此目的：（巨大的夜间睡眠恢复后重新审视配方）

=IF($C2>0,INDIRECT("'tickets_info'!B" & $C2)
  & " " & INDIRECT("'tickets_info'!c" & $C2)
  ...
  & " " & INDIRECT("'tickets_info'!N" & $C2),"")

或（甚至更长）

=IF($C2>0,INDIRECT(ADDRESS($C2,2,,,"tickets_info"))
  & " " & INDIRECT(ADDRESS($C2,3,,,"tickets_info"))
  ...
  & " " & INDIRECT(ADDRESS($C2,14,,,"tickets_info")),"")

然后复制下来。这样你就不再需要“tickets_info”上的 O 列公式了（因此不需要以任何方式修改它）。

请注意，我没有写所有这些“重复”的代码块。显然，为了使公式正常工作，...必须替换这些，并添加剩余的必要编码块，并增加列引用对于您需要显示的每一列。

本次编辑的最后说明：最终公式会很长，但如果我们不想使用 C2 上的中间值公式，而是在该单元格上使用独特的组合 C2/D2 公式，那么最终公式可能会非常庞大​​（但在我看来，这非常尴尬，所以我绝对不会告诉你怎么做！）

第二种方法“每个值一个单元格”- 更新：

如果您想要在“tickets”表的单独列中显示“tickets_info”表 B2 到 N2 的值，那么可以这样做：

  1. C2细胞放公式=IFERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),0)；

  2. D2细胞培养公式（旧方法） =IF($C2>0,INDIRECT("'tickets_info'!B"&$C2),"")；

    （更新的替代方案） =IF($C2>0,INDIRECT(ADDRESS($C2,COLUMN()-1,,,"tickets_info")),"")；

  3. （如果按照以前的方式）将 D2 上的公式复制/粘贴到 E2 到 P2 单元格间隔，但确保在复制公式的其他列B上将!和之间的公式更改（“增加”）&到必要的C、、D...；N

    （如果使用更新的替代方案 - 我的首选） 这里需要理解的重要一点是，使用COLUMN()公式可以让您在将公式复制到相邻列时无需记住“逐步”更改!B"- 这难道不是自由吗？;-) 然后您可以简单地在 D2：P2 单元格间隔上复制/粘贴公式，而不必更改任何内容，因此（无法说明性能但）如果您想避免因复制公式后忘记增加引用而导致的错误风险，这肯定是最好的选择……或者即使您只是懒得编写/修改重复的代码块（...就像我一样！;-D）

  4. 选择 C2 到 P2 单元格间隔并将公式复制到第 500 行（或其他行）。

  5. （可选）隐藏 C 列。

就是这样。

关于这种方法的最后一点说明（深入分析）：我经常MATCH喜欢VLOOKUP 但，在这种情况下，我直接使用它，因为一开始，我认为您需要一种方法来通过一次查找检索“tickets_info”上整个对应的行值；即使是在 C 列上计算的中间值也基本上是出于同样的原因。一旦您需要单个值，您甚至可以摆脱它们，结合 C/D 列公式如下：

=IF(ISERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0)),"",INDIRECT(ADDRESS(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),COLUMN()-1,,,"tickets_info")))

然后将其复制/粘贴至 C2:O500 单元格间隔。

最后，在这种情况下，正如 fixer1234 所建议的那样，VLOOKUP成为这样的一个可能的替代方案（写得更短一点） 方式：

=IF(ISERROR(VLOOKUP($A2,'tickets_info'!$A$2:$N$5000,COLUMN()-1,FALSE)),"",VLOOKUP($A2,'tickets_info'!$A$2:$N$5000,COLUMN()-1,FALSE))

PS 如果复制/粘贴公式后遇到格式问题该怎么办？

  • 如果你选择第二方法，只需使用您需要的格式来格式化单元格（即日期和时间值的日期/时间）；您可以为此使用常规单元格格式，或者根据需要定义自定义格式，或者更简单地（强烈推荐！）复制工作表“tickets_info”上的原始列（从中检索值并粘贴/仅特殊格式到相应的“tickets”工作表复制目标列）。

  • 如果你选择第一的一，您可能需要使用 TEXT 函数将一些检索到的数值转换为格式化的文本。在这种情况下要注意的一点是，使用 TEXT 函数，您将无法使用原始值进行操作，例如，日期或时间计算、测试日期是否满足某些条件等。

因此，建议仅当/如果出现以下情况时才采用第一种方法：

A）您只是处理检索文本值（数字或任何已存储在文本格式的列中的内容），因为它不会产生任何区别；

或者b)一个简单的值的连接副本就足够了，并且您不打算确定是否需要单独使用它们来进行任何操作。

...否则就不要去想它，并始终闭上眼睛遵循第二种方法，它肯定不会受到伤害。

现在，终于得出结论，我添加了 fixer1234 针对后面讨论的问题提出的一些链接建议：

如何创建自定义数字格式（包含完整的代码）。

解释如何使用 TEXT 函数将提供的数值转换为文本。

答案2

我遇到了同样的问题并使用了以下方法：

  • 在Tickets工作表中，选择 C2 至 O2

  • 无需单击任何地方，开始输入：

    =VLOOKUP(A2, 票务信息!$A$2:$N$5000, {2,3,4,5,6,7,8,9,10,11,12,13,14,15}, FALSE)

  • 同时按下Ctrl+ Shift+Enter
  • 自动填充

答案3

我想我可能遗漏了这方面的一些东西，但在我看来，好像您在“第二页”（输出页）的 A 列中有值，并且想要在 B 到 N 列中找到它们的匹配数据。它们将是直接匹配：如果某一行的 A 列值存在于“第一页”（源）表中，则其 F 列值将进入输出表行的 F 列，等等。输出表上 A 列中的某些值与源表上的任何值都不匹配，因此该行应该为空白。

如果是这样，只需在 B 列中执行 VLOOKUP()，检查源材料的 A 列中 A 列的值，并返回它在源材料的第 2 列（简称 B 列）中找到的值。

请注意，在输出页面的第 2 列中，您需要来自源工作表第 2 列的数据。以此类推。下面是转折点：

对于要返回参数的列（第 3 个参数），请不要使用手输入的数字，而是使用“ Column() ”，这样每个列的单元格都会获得正确的源列的值。

没有什么比这更聪明的了。虽然有很多 VLOOKUP()，但这可能没问题。我的普通设置对于 5,000 行来说没问题，所以我猜你的设置也是没问题的。

但是...如果它不匹配任何东西呢？好吧，对 B 列 VLOOKUP() 使用 IF() 测试，如果该行中的 A 列与源 A 列中的任何内容都不匹配，则返回“”。

现在，魔法来了。谁愿意依赖“可能没问题”或者被限制在 5,000 行左右，甚至两倍，然后窒息？没有人。

Excel 尽可能从左到右计算公式，并在得到结果时停止计算。如果它首先有一个 IF() 测试并且其结果 = TRUE，它将跳过任何其他内容并转到 TRUE 的结果（如果结果是“8”或“树”这样的“常量”，则停止，或者如果它本身是一个或两个函数，则评估那里的内容。因此，如果您向 C 列到 N 列中的所有单元格添加一个 IF() 测试，如下所示：

=IF( $B2 = "", "", VLOOKUP( $A2, tickets_info!$A$2:$N$5000, COLUMN(), FALSE))

然后，如果 A 列值不匹配，则从 C 列向右的所有列将迅速评估为“”，并且永远不会执行 VLOOKUP()！（在这种情况下，TRUE 为“”，它是一个常量，不需要进一步评估，因此 Excel 停止并显示“”。）因此不再有“可能没问题”，不会很快出现阻塞。如果输出页面上只有 943 行值需要检查，并且只有 634 行与源页面上匹配，则只执行 634 * 12 = 7,608 个额外的 VLOOKUP()，而不是 5,000 * 12 = 60,000。

此外，如果您的源数据永远不会改变（一旦在源表上，它可能会被向上或向下排序，但永远不会被编辑），那么您可以始终定期复制一部分输出单元格并粘贴|特殊|值以消除它们的公式，这将进一步减少“负载”。

相关内容