Excel多条件查找：INDEX/MATCH与FILTER函数详解
Excel多条件查找：INDEX/MATCH与FILTER函数详解
在Excel中进行多条件查找时，INDEX/MATCH函数和FILTER函数是两种非常有效的解决方案。本文将详细介绍这两种方法的工作原理，并通过实际示例帮助读者掌握它们的使用技巧。
Excel多条件查找方法
在Excel中基于两个条件进行查找有多种方法。本页将展示如何使用以下两种方法进行双条件查找：
- INDEX/MATCH函数（适用于所有版本的Excel）
- FILTER函数（适用于具有动态数组功能的Excel版本，如Excel 365）
接下来的部分将帮助你选择最适合的解决方案，并通过两个简短视频演示每种方法的工作原理。选择解决方案后，下面将详细介绍如何设置FILTER函数或Excel的INDEX和MATCH函数。
应该使用FILTER还是INDEX/MATCH？
为了帮助你决定在Microsoft Excel中使用哪种多条件查找解决方案，以下是它们之间的关键差异：
INDEX/MATCH函数
- 这两个函数在所有版本的Excel中都可用
- 如果满足条件的结果有多个，则返回范围中的第一个结果
- 多条件公式必须以数组形式输入，除非Excel版本支持动态数组
FILTER函数
- 该函数仅在你的Excel版本支持动态数组（如Excel 365）时可用。确保需要使用工作簿的其他人也具有动态数组功能
- 如果满足条件的结果有多个，则结果将向下溢出到下面的行，以显示所有项目
- 不需要以数组形式输入
其他解决方案
此外，还有其他方法可以使用Microsoft Excel进行多条件查找。请查看以下页面上的示例，包括Excel LOOKUP和VLOOKUP函数示例：
此外，还可以查看有关Excel不同查找函数的更多信息。
视频教程：Excel双条件查找
为了帮助你入门，下面是两个视频，展示了每种查找选项的工作原理。视频下方有详细的书面步骤：
1) Excel多条件查找：演示了INDEX和MATCH函数如何一起工作，基于一个条件。接下来，公式将更改为基于两个条件。要跟随视频，请下载示例文件。
2) 开始使用Excel FILTER函数：演示了FILTER函数如何工作，基于一个条件。在6:00标记处的第三个示例显示了带有两个条件的FILTER函数。要跟随视频，请下载示例文件。
由于技术限制，此处无法直接观看视频，请点击链接查看。
INDEX和MATCH
要使用多个条件进行Excel查找，可以使用INDEX和MATCH函数。
- INDEX函数可以从列表或数据范围中的特定位置返回值
- MATCH函数可以在列表中查找项目的的位置
当INDEX和MATCH一起使用时，它们创建了一个灵活且强大的查找公式。
简单的INDEX和MATCH
在使用INDEX和MATCH进行多条件查找之前，让我们看看它们如何在简单公式中一起工作。
在下面的公式中，我们需要在价格列表的B列中找到“Sweater”，并从同一工作表的C列中获取其价格。
- 项目条件已输入单元格A7 - Sweater
- 此INDEX和MATCH公式已输入单元格C7，用于获取该项目的价格：
=INDEX($C$2:$C$4,MATCH(A7,$B$2:$B$4,0))
该公式正确返回了毛衣的价格 - 10。
简单INDEX MATCH公式的工作原理
让我们看看这个简单的INDEX / MATCH公式如何找到毛衣的正确价格。
- 首先，MATCH函数在B2:B4单元格中查找"Sweater"的位置，并返回该数字
- 然后，INDEX函数从C2:C4单元格中返回该行的价格
因此，通过结合使用INDEX和MATCH，你可以找到项目的行位置，并返回该行的价格。
这里是函数的详细信息：
- MATCH
MATCH函数有3个参数：
- lookup_value：要在查找数组中查找的值是什么？
- lookup_array：查找数组在哪里？
- [match_type]：（可选）MATCH应该查找精确匹配（0）还是近似匹配（1或-1）？
在这个例子中，MATCH函数在B2:B4范围内查找A7单元格中的值，使用精确匹配类型。
- 结果是1，因为"Sweater"在该范围的第一行。
- INDEX
INDEX函数有3个参数：
- array：数组在哪里？
- row_num：哪个行包含要返回的值？
- [column_num]：（可选）哪个列包含要返回的值？
在这个例子中，INDEX函数返回10 - C2:C4范围内第一行的价格。
INDEX/MATCH双条件查找
在上一个示例中，匹配基于一个条件 - 项目名称。对于下一个条件查找，有两个条件 - 项目和大小。
在这个定价查找表中，每个项目列出3次 - 每个大小一次。我们想要查找特定产品和大小的价格。
- 项目：Jacket - 输入单元格C13
- 大小：Large - 输入单元格D13
大号夹克的价格是40，如果在单元格E13中输入INDEX/MATCH公式，应该得到这个结果。
注意：A列中的数字仅用于参考 - 它们在公式中不使用。
INDEX/MATCH双条件公式
要基于两个条件计算价格，请在单元格E13中输入以下数组输入的INDEX和MATCH公式。该公式将在下面解释。
注意：在具有溢出函数的Excel 365中，只需按Enter键。不需要数组函数。
- =INDEX(E2:E10,
MATCH(1,
(C13=$C$2:$C$10) * (D13=$D$2:$D$10),0))
数组输入 - 按*Ctrl + Shift + Enter，而不是仅按Enter键。这将自动在数组公式周围添加花括号。
双条件公式的工作原理
让我们看看这个INDEX MATCH多条件公式如何工作。
INDEX函数
产品价格在单元格E2:E10中，INDEX将从该范围内返回一个价格。
- =INDEX(E2:E10,
MATCH函数 - 查找行
MATCH函数告诉INDEX使用该范围中的哪个行号。
注意：这个数字可能与工作表行号不同。
- 在MATCH函数中，第一个参数，lookup_value，是1
- MATCH(1,
2a) 对于第二个参数，lookup_array，有两个测试：
- 第一个条件：在项目列中是否有Jacket - TRUE（1）或FALSE（0）
- 第二个条件：在大小列中是否有Large - TRUE或FALSE
2b) 接下来，将这些TRUE FALSE值相乘，返回零和一
2c) 在这个例子中，只有第8个产品同时满足两个条件，夹克和大号，并返回1
- 如果在公式栏中评估lookup_array参数，数组的第8个位置将有一个1。
- MATCH函数的第三个参数，match_type，是零，以返回精确匹配。
INDEX/MATCH结果
因此，MATCH函数的结果是8，因为它在lookup_array的第8个位置找到了lookup value的精确匹配。
基于这个结果，INDEX返回价格查找表中第8个产品的价格。
FILTER函数
如果你的Excel版本支持动态数组（Office 365），你可以使用新的FILTER函数返回所需的结果。FILTER函数允许你基于条件从范围内返回结果。
在这个例子中，有一个名为tblProducts的命名表，包含4列 - Code、Item、Size和Price。
条件单元格
在工作表顶部，输入了两个条件，用于产品规格
- 单元格A2 - 产品的项目名称：Jacket
- 单元格B2 - 产品的大小：Large
基于这些条件，我们需要两个结果 - 产品的价格和产品的字母数字代码。
使用FILTER函数获取产品价格
首先，在单元格C2中，我们将输入一个动态数组FILTER公式，基于A2和B2单元格中的条件计算产品价格。
FILTER函数有3个参数：
- array
- include
- if_empty（可选）
1) array参数
产品价格存储在以下范围中：
- 在名为tblProduct的表中
- 在名为Price的列中。
因此，从公式开始，
- 输入等号：=
- 输入函数名称，FILTER
- 输入左括号 - (
- 在工作表上，单击价格列标题单元格
Excel将自动输入对价格列的引用，使用结构化表引用。
公式应该如下所示：
- =FILTER(tblProducts[Price],
2) include参数
对于include参数，公式必须检查Item列，看是否与单元格A2匹配
- (tblProducts[Item]=A2)
AND公式必须检查Size列，看是否与单元格B2匹配
- (tblProducts[Size]=B2)
两个条件都在include参数中，用乘法运算符（*星号）分隔
=FILTER(tblProducts[Price], (tblProducts[Item]=A2) * (tblProducts[Size]=B2))
使用FILTER获取产品代码
返回产品代码的FILTER公式几乎相同，但对于array参数，我们需要从Code列中获取结果：
=FILTER(tblProducts[Code], (tblProducts[Item]=A2) * (tblProducts[Size]=B2))
下载Excel文件 ✅
- INDEX/MATCH：要跟随INDEX/MATCH视频，请下载压缩的Lookup with Multiple Criteria示例文件。工作簿是xlsx格式，不包含任何宏。
- FILTER函数示例：要查看四个FILTER示例，请下载FILTER function sample workbook。文件是压缩的，格式为Excel xlsx，没有宏。