如果您在此处，则意味着您正在尝试使用VLOOKUP从数据集中检索多个值。但是，简单点说，VLOOKUP无法返回多个值。但这并不意味着我们无法做到。我们可以进行查找以检索多个值，而不是仅第一个。

286

通用公式

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

数组：您要从中获取数据的范围。

lookup_value：您要过滤的lookup_value。

lookup_value_range：您要过滤lookup_value的范围。

lookup_value范围中的第一个单元格：如果lookup_value范围为$ A $ 5：$ A $ 100，则其值为$ A $ 5。

重要提示：所有内容均应为“ link：/ excel-range-name-absolute-reference-in-excel [absolute referenced]”。 lookup_value可以根据需要是相对的。

输入它作为数组公式。编写公式后，按CTRL + SHIFT + ENTER键使其成为数组公式。

查找多个结果的示例我有此学生数据在范围A2：E14中。在单元格G1中，我有一个区域值的下拉列表，例如中部，东部，北部，南部和西部。

现在，无论我在G1中的哪个区域，我都希望在H列中显示该区域所有学生的列表。

287

要在excel中查找多个值，请确定我们的变量。

数组：$ C $ 2：$ C $ 14 lookup_value：$ G $ 1 lookup_value_range：$ A $ 2：$ A $ 14 lookup_value范围的第一个单元格：$ A $ 2根据上述数据，我们在excel中检索多个值的公式将是：

{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}

在H2中复制此公式，然后按CTRL + SHIFT + ENTER。现在，向下拖动此公式，直到出现#NUM错误。 #NUM表示没有其他与该lookup_value匹配的剩余值，它是列表的末尾。

288

如果#NUM惹恼了您，则可以在公式前面使用“ link：/ logical-formulas-excel-iferror-function [IFERROR function]”，并显示一些有意义的信息而不是错误。

{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}

当显示所有项目时，将显示—​List Ends。

现在让我们了解它的工作原理。

INDEX function”来检索值。

因此，主要的挑战是获取lookup_value的索引号数组。要获取索引号，我们使用了“ link：/ tips-if-excel-excel [IF]”和“ link：/ lookup-and-reference-excel-row-function [ROW]”

功能。这个公式的确确实很复杂，让我们分解一下。

我们想从学生列中获取值，因此我们的`link：/ lookup-formulas-excel-index-function [INDEX function]`数组为$ C $ 2：$ C $ 14。现在，我们需要从$ A $ 2：$ A $ 14（查找值）中给出G1的值所在的行号（现在，假设G1在其中居中）。

IF（$ G $ 1 = $ A $ 2：$ A $ 14，ROW（$ A $ 2：$ A $ 14）：现在，如果单元格的G1值（中心）在$ A $ 2：$ A范围内，则此部分返回行号$ 14 else返回FALSE。在此示例中，它将返回

\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}。

  • *现在，由于上面的数组包含第一行（1：1）的行号，因此我们需要从数组（A2：A14）开始的行，为此，我们使用-ROW（$ A $ 2） IF公式中的+1，这将返回数组开始之前的行数

在此示例中为-1；如果从A3开始，则将返回-2，依此类推。将从IF返回的数组中的每个数字中减去数字，因此最终得到IF（$ G $ 1 = $ A $ 2：$ A $ 14，ROW（$ A $ 2：$ A $ 14）-ROW（$ A $ 2）+1）这将转换为\ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}。

  • 接下来，此数组由`link：//包围statistics-formulas-excel-small-function [SMALL]`

函数，此函数返回第N个小给定数组中的est值。现在，我们有了`link：/ statistical-formulas-excel-small-function [SMALL]`（\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE} ，ROW（1：1））。

ROW（1：1）将返回1。因此，上述函数将返回数组中的第1个最小值，即2。

当您在下面的单元格中复制此公式时，ROW（1：1）将变为ROW（2：2），它将返回数组中的第二个最小值，即7，依此类推。这允许函数首先返回第一个发现的值。但是，如果要首先获取最后找到的值，请使用LARGE函数而不是SMALL函数。

现在使用上面函数返回的值，INDEX函数可以轻松地返回范围中的每个匹配值。

因此，是的，您可以通过一个查找值来查找多个对应的值。在上面的gif文件中，我使用了IFERROR函数来捕获错误和条件格式，以使其看起来有点可视。在上面的示例中，我使用了其他文章中介绍的许多其他功能和技术。

下载文件：