☰

根据两列或更多列搜索字段

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
根据两列或更多列搜索字段

我想professor根据黄色字段中的输入输出字段。输入将是或sub_code的可能性。我尝试过和，但在尝试执行 时卡住了：。column Dcolumn GVLOOKUP(...)INDEX(...,MATCH(...))either column D or column G

例如，我希望John在打字时在E7打印Ag1，或者Ps1在E8打印。

我该如何为这种情况编写一个函数？

电子表格表

答案1

选项1：

要解决这个问题，你需要一个辅助细胞设置您想要使用的代码的选择。

在此处输入图片描述

在单元格中写入此公式F10。

=IFERROR(INDEX($A$3:$A$6,IF($C$9=1,MATCH($D$10,$D$3:$D$6,0),IF($C$9=2,MATCH($D$10,$G$3:$G$6,0)))),"Wrong Code")

怎么运行的：

  • 在辅助单元格中C9，写下您的选择 1 或 2。
  • Sub Code在单元格中写入D10。
  • 公式将Sub Code在Column Dif中搜索C9 has 1，否则Sub Code检查Column G.
  • 如果你在单元格中设置了错误的组合C9，D10那么公式将返回错误代码错误。

編輯：

选项 2：

此数组公式也可以在单元格中使用F10。

{=IFERROR(INDEX($A$3:$A$6,MATCH(1,($D$3:$D$6=$D$10)+($G$3:$G$6=$D$10),0)),"Wrong Code")}

笔记，

  • 用 完成上面写的公式Ctrl+Shift+Enter。
  • 在与选项 2 不需要在 Helper Cell 中写入 Value C9，只需Sub Code在 Cell 中写入D10。

根据需要调整公式中的单元格引用。

答案2

=IFERROR(INDIRECT("A"&IF(SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5))=0, SUMPRODUCT(--($G$2:$G$5=$E$8),ROW($A$2:$A$5)), SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5)))), IF(E8="","","Invalid code"))

我承认，它不是最短的，但在我看来它是最稳定的。Fixer1234 承认他的代码不太优雅，而 Rajesh 使用了辅助单元格，这不是最佳实践，因为 Excel 不是为它们设计的，当你处理更复杂的项目时可能会导致循环引用。

不过，回到我的公式。

怎么运行的

SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5)：检查 D 列是否包含匹配项。如果包含，则返回匹配项的行号。如果未找到匹配项，则返回 0。

SUMPRODUCT(--($G$2:$G$5=$E$8),ROW($A$2:$A$5)：检查 G 列是否包含匹配项。如果包含，则返回匹配项的行号。如果未找到匹配项，则返回 0。

IF(...=0, ..., ...)：检查 D 列的搜索是否返回 0，即无匹配项。在这种情况下，它返回 G 列的结果。否则，它仅返回 D 列的结果。

INDIRECT("A"&...)：行号从 SUMPRODUCTs（或 0，如果没有匹配）与“A”组合 - 第一列的字母。 INDIRECT 然后，函数会查找具有该引用的单元格。例如，它可以找到名为“A3”的单元格，或者，如果未找到匹配项，则找到“A0” - 一个不存在的单元格。

IFERR(..., ...)：检查并处理错误。如果 D 列不包含匹配项，则它将返回对 G 列的搜索。但是，如果 G 列也不包含任何内容，则“A0”将直接传递给 INDIRECT 函数，它不是真正的单元格。因此，它将导致错误，并且它将运行错误处理程序而不是显示错误。

IF(E8="","","Invalid code"))：处理错误。如果输入单元格为空，则也保持空白，因为无需大惊小怪。但是，如果它不为空，但仍有错误，则返回'无效的代码'，因为它只能意味着输入了某些内容，并且这些内容无效。

例子

功能上：

E8 中写入 'He1'，E7 返回 'Phillip'。

E8 中写入‘Kh1’，E7 返回‘Deav’。

空输入：

E7 和 E8 均为空

输入无效：

E8 中写入‘Rn1’，E7 返回‘无效代码’

答案3

VLOOKUP 需要搜索最左边的列，所以这行不通。INDEX + MATCH 可以工作，但 MATCH 不能搜索多个或二维范围。一种解决方法是将多个 MATCH 组合到不同的范围内。

在此处输入图片描述

E7 中的查找结果使用以下公式：

=IFERROR(INDEX(A2:A5,IFERROR(MATCH(E8,D2:D5,0),0)+IFERROR(MATCH(E8,G2:G5,0),0)),"Invalid Sub_code")

每列都有自己的匹配项。匹配项只会出现在一列中，因此如果该列没有匹配项，IFERROR 将返回零。然后添加匹配值将合并匹配列的位置索引和任何其他搜索列的零。问题标题说“两列或更多列”。只需为每个附加列添加另一个 MATCH 表达式即可扩展这一点。

INDEX 被其自己的 IFERROR 包裹，以防输入错误的 Sub_code。MATCH 不区分大小写，因此只要大小写不同，它仍会找到匹配项。

当然，您可以有多个输入单元格和相应的结果单元格。

选择

@Rajesh S 注意到这也可以重组：

=IFERROR(INDEX(A2:A5,MATCH(E8,D2:D5,0)),IFERROR(INDEX(A2:A5,MATCH(E8,G2:G5,0)),"Invalid Sub_code"))

这将使用带有 INDEX 的每个 MATCH 表达式。INDEX 上的 IFERROR 还会处理任何 MATCH 错误。然后 IFERRORS 会嵌套，因此第一次查找中的错误会转到第二次查找，而第二次查找中的错误会转到糟糕的 Sub_code 警告。它最终的长度相同，因为虽然 IFERROR 函数计数减少，但 INDEX 函数计数增加。可以通过额外的嵌套来扩展以适应其他列。

使用看起来最直观的版本。

为了比较结构和逻辑，我将公式分解开来。

方案一：

=IFERROR(
       INDEX(A2:A5,
                 IFERROR( MATCH(E8,D2:D5,0) ,0) + IFERROR( MATCH(E8,G2:G5,0) ,0) )
        ,"Invalid Sub_code")

方案 2：

=IFERROR( 
         INDEX(A2:A5, MATCH(E8,D2:D5,0) )
         ,IFERROR(
                  INDEX(A2:A5, MATCH(E8,G2:G5,0) )
                  ,"Invalid Sub_code")  )

相关内容