Excel中查找时返回全部匹配结果的两种方法

有下面的数据：

需要查找所有苹果的记录：

一般情况下，Excel中查找是通过VLOOKUP进行的，

• 
  

=VLOOKUP(M3,tblData[[产品]:[计划]],{1,2,3,4},0)

但是，VLOOKUP函数只能返回第一条记录：

而我们需要返回全部（4条）记录：

我们可以使用两种方法解决，详情请看下面的视频：

1.  INDEX+MATCH
   

---

如果你使用Excel 2019及之前的版本，那么可以使用下面的公式：

• 
  

={INDEX(tblData,SMALL(IF(tblData[产品]=M3,ROW(tblData[产品])-2,10000),{1;2;3;4}),{1,2,3,4,5,6,7})}

这是一个数组公式，如果你的Excel不支持动态数组（即传统Excel版本），就需要使用CTRL+SHIFT+ENTER完成公式的输入；如果支持动态数组，直接输入即可以得到结果。

下面简单解释一下，

我们将上面的公式用代号改写一下：

=INDEX(源数据，满足条件的记录行号，返回的列号)

这个公式是使用INDEX函数完成的，起作用的方式就是从源数据区域中返回指定行号和列号的单元格。

公式中返回的列号使用的是数组：

• 
  

{1,2,3,4,5,6,7}

表示返回“源数据区域”中的从第1列到第7列的数据，这样就可以返回满足条件的整行数据了。

而“满足条件的记录行号”，是用下面的公式得到的：

• 
  

SMALL(IF(tblData[产品]=M3,ROW(tblData[产品])-2,10000),{1;2;3;4})

其中的IF部分是个数组：

• 
  

IF(tblData[产品]=M3,ROW(tblData[产品])-2,10000)

作用是将源数据中的产品列与条件进行比较，所有满足条件的记录返回对应的行号，而不满足条件的记录则返回10000（一个比较大的数值即可）。

可以看出，其中第一个匹配的结果就是上述公式返回2的对应行，这可以通过下面的公式得到：

• 
  

SMALL(IF(tblData[产品]=M3,ROW(tblData[产品])-2,10000),1)

即通过SMALL函数返回匹配行号数组中第一小的值。

有了这个行号，就可以使用下面的公式返回第一个匹配的行

了：

• 
  

=INDEX(tblData,SMALL(IF(tblData[产品]=M3,ROW(tblData[产品])-2,10000),1),{1,2,3,4,5,6,7})

而原公式中的SMALL部分，使用数组{1;2;3;4}，同时返回前四个最小值：

• 
  

SMALL(IF(tblData[产品]=M3,ROW(tblData[产品])-2,10000),{1;2;3;4})

这样就可以同时返回这四行记录了。

2. FILTER

---

上述方案理解起来比较困难，使用时也比较麻烦：需要事先知道有多少满足条件的记录，然后选定结果需要占用的单元格区域，然后用CTRL+SHIFT+ENTER键进行输入。

所以只能是一个在传统Excel版本中的折中方案。

如果使用的是Excel 2021或者Office 365，就可以使用FILTER函数，

• 
  

=FILTER(tblData,COUNTIFS(M3:M3,tblData[产品]))

直接对源数据进行筛选，条件是产品列是否在条件区域出现过。

很简单的一个公式就可以解决问题，而且可以筛选满足多个条件的全部记录。

---