excel - 类似于 VLookup 的函数，但返回每个相关的值

Question

我很难找到一个函数，该函数允许我获取数据列表并返回与引用单元格相关的所有值。它的想法就像 Vlookup 一样，除了 Vlookup 只返回它看到的第一个值。

我曾考虑过在我的列表中使用数据透视表和/或使用过滤器，但由于工作簿的复杂性，我遇到了这些问题。

这基本上就是我的工作簿的样子:

    A         B       C
 1 Pen      Blue     200
 2 Marker   Red      150
 3 Pen      Black    180
 4 Pen      Green    170
 5 Marker   Green    125

我试图找到一个函数来返回这个:

    A       B       C
 1 Pen    Blue     200
 2 Pen    Black    180
 3 Pen    Green    170

我需要做的就是将 A 列的值识别为“Pen”，并在 B 列中带回颜色。C 列然后我可以使用 Vlookup 来获取。

我觉得我缺少一些东西来帮助别人理解并能够帮助我，但我无法确定它是什么......如果你需要其他东西作为澄清，请告诉我

Answer 1

这可以通过数组公式来完成。

首先，用数字 1 填充第一列到查找范围中“笔”的数量。

然后，输入公式:

=INDEX(A$1:A$5,SMALL(IF($A$1:$A$5="Pen",ROW($A$1:$A$5),""),$A11))

其中 $A11 是您从第一步开始枚举的单元格。

然后按 ctrl+shift+enter 使其成为数组公式。

“if”返回一个行号数组，其中单元格值为“Pen”(如果需要，显然可以将其更改为其他值)。然后small返回第n个最小的，这就是为什么我们需要第一列是数字1到范围内的“Pen”数字。然后索引返回该行指定范围内的结果单元格。请注意，此公式假定您的表格从 A1 开始(根据您的示例)，但如果不是这种情况，可以轻松调整。

然后，您可以将其向右和向下拖动，只要您将 $ 保持在它们现在所在的位置，公式就会适应。

输出将如下所示(假设您像我一样从 A11 开始):

     A       B       C        D
 11  1      Pen    Blue     200
 12  2      Pen    Black    180
 13  3      Pen    Green    170