Excel 多条件查找 #

┌─────────────────────────────────────────────────────────────┐
│                    多条件查找方法                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  方法1：辅助列 + VLOOKUP                                    │
│  方法2：INDEX + MATCH 数组公式                              │
│  方法3：SUMPRODUCT（数值返回）                              │
│  方法4：FILTER 函数（Excel 365）                            │
│  方法5：XLOOKUP（Excel 365）                                │
│                                                             │
└─────────────────────────────────────────────────────────────┘

数据：
    A          B          C          D          E
1   部门       产品       辅助列     销售额     
2   销售       产品A      =A2&"-"&B2  1000      
3   技术       产品B      =A3&"-"&B3  2000      
4   销售       产品B      =A4&"-"&B4  1500      

查找销售部产品B的销售额：
=VLOOKUP("销售-产品B", C:D, 2, FALSE)  → 1500

优点：
- 简单易懂
- 兼容所有版本

缺点：
- 需要添加辅助列
- 数据量大时影响性能

=INDEX(返回范围, MATCH(1, (条件1)*(条件2)*(条件3), 0))

传统 Excel：按 Ctrl+Shift+Enter
Excel 365：直接 Enter

数据：
    A          B          C          D
1   部门       产品       月份       销售额
2   销售       产品A      1月        1000
3   销售       产品B      1月        2000
4   技术       产品A      1月        1500
5   销售       产品A      2月        1200

查找销售部产品A在1月的销售额：
=INDEX(D:D, MATCH(1, (A:A="销售")*(B:B="产品A")*(C:C="1月"), 0))
→ 1000

满足任一条件：
=INDEX(D:D, MATCH(1, (A:A="销售")+(A:A="技术"), 0))

返回第一个满足条件的值

优点：
- 不需要辅助列
- 灵活性高

缺点：
- 数组公式复杂
- 大范围时性能较差

=SUMPRODUCT((条件1)*(条件2)*(条件3)*返回范围)

数据：
    A          B          C          D
1   部门       产品       月份       销售额
2   销售       产品A      1月        1000
3   销售       产品B      1月        2000

查找销售部产品A在1月的销售额：
=SUMPRODUCT((A2:A10="销售")*(B2:B10="产品A")*(C2:C10="1月")*D2:D10)
→ 1000

SUMPRODUCT 只适用于数值返回：
- 如果有多条匹配记录，返回总和
- 如果没有匹配记录，返回 0
- 不适用于文本返回

=FILTER(返回范围, (条件1)*(条件2)*(条件3))

Excel 365 专用

数据：
    A          B          C          D
1   部门       产品       月份       销售额
2   销售       产品A      1月        1000
3   销售       产品B      1月        2000

查找销售部产品A在1月的销售额：
=FILTER(D:D, (A:A="销售")*(B:B="产品A")*(C:C="1月"))
→ 1000

返回第一个结果：
=INDEX(FILTER(D:D, (A:A="销售")*(B:B="产品A")*(C:C="1月")), 1)

返回所有匹配的行：
=FILTER(A:D, (B:B="产品A")*(C:C="1月"))

返回所有产品A在1月的数据

=FILTER(D:D, (A:A="销售")*(B:B="产品A"), "未找到")

找不到时返回"未找到"

=XLOOKUP(1, (条件1)*(条件2)*(条件3), 返回范围)

数据：
    A          B          C          D
1   部门       产品       月份       销售额
2   销售       产品A      1月        1000
3   销售       产品B      1月        2000

查找销售部产品A在1月的销售额：
=XLOOKUP(1, (A:A="销售")*(B:B="产品A")*(C:C="1月"), D:D)
→ 1000

=XLOOKUP(1, (A:A="销售")*(B:B="产品A"), D:D, "未找到")

数据：
    A          B          C          D
1   部门       工号       姓名       职位
2   销售       E001       张三       经理
3   技术       E002       李四       工程师
4   销售       E003       王五       专员

查找销售部工号E003的职位：
=INDEX(D:D, MATCH(1, (A:A="销售")*(B:B="E003"), 0))
→ "专员"

Excel 365：
=XLOOKUP(1, (A:A="销售")*(B:B="E003"), D:D)
→ "专员"

数据：
    A          B          C          D
1   产品       规格       颜色       价格
2   产品A      大         红色       100
3   产品A      小         红色       80
4   产品B      大         蓝色       150

查找产品A大号红色的价格：
=INDEX(D:D, MATCH(1, (A:A="产品A")*(B:B="大")*(C:C="红色"), 0))
→ 100

数据：
    A          B          C
1   日期       产品       销售额
2   2024-01-01 产品A      1000
3   2024-01-02 产品A      1200
4   2024-01-03 产品B      1500

查找2024-01-02产品A的销售额：
=INDEX(C:C, MATCH(1, (A:A=DATE(2024,1,2))*(B:B="产品A"), 0))
→ 1200

不推荐：
=INDEX(D:D, MATCH(1, (A:A="销售")*(B:B="产品A"), 0))

推荐：
=INDEX(D2:D100, MATCH(1, (A2:A100="销售")*(B2:B100="产品A"), 0))

限制范围可以提高性能

=INDEX(表格[销售额], 
       MATCH(1, (表格[部门]="销售")*(表格[产品]="产品A"), 0))

表格会自动扩展范围