如何使用XLOOKUP实现多条件模糊匹配（含文本包含判断）

一、Excel公式方案（解决XLOOKUP的兼容问题） #

针对单条件/多条件兼容、文本包含匹配、AND函数数组失效的问题，用数组逻辑与（*）代替AND+空条件自动跳过的思路，直接用XLOOKUP覆盖所有场景：

最终公式 #

在需要赋值的Value单元格（比如D2）输入以下公式（Excel 365/2021可直接回车，旧版本需按Ctrl+Shift+Enter生效）：

=XLOOKUP(1,
  --(IF(Criteria!A$2:A$3="",TRUE,ISNUMBER(SEARCH(Criteria!A$2:A$3,$A2)))) *
  --(IF(Criteria!B$2:B$3="",TRUE,ISNUMBER(SEARCH(Criteria!B$2:B$3,$B2)))) *
  --(IF(Criteria!C$2:C$3="",TRUE,ISNUMBER(SEARCH(Criteria!C$2:C$3,$C2)))),
  Criteria!E$2:E$3,
  0
)

关键细节说明 #

• 单条件场景兼容：如果条件表中某列单元格为空，IF直接返回TRUE，相当于该条件不做限制，自动满足
• 文本包含匹配：ISNUMBER(SEARCH(条件, 当前单元格))判断当前单元格是否包含条件文本（不区分大小写，需区分则替换为FIND）
• 数组多条件逻辑：用*代替AND——AND在数组中只会返回单个布尔值，而*可对数组每个元素做逻辑与运算，生成1/0数组，XLOOKUP定位值为1的匹配行
• 默认值设置：最后一个参数0是未匹配时的返回值，可按需修改

二、PowerQuery方案（适合批量/动态更新场景） #

如果数据量较大或需要频繁修改条件，PowerQuery的批量匹配更高效易维护：

操作步骤 #

1. 加载数据到PowerQuery

   • 选中原始数据区域，点击「数据」→「从表格/区域」，加载后命名为RawData
   • 同样加载条件表（Criteria工作表），命名为CriteriaTable

2. 添加匹配自定义列
   回到RawData的查询编辑器，点击「添加列」→「自定义列」，输入以下M语言公式：

   let
       // 筛选符合当前行的条件行
       MatchRows = Table.SelectRows(CriteriaTable, 
           each ([A] = "" or Text.Contains([@A], [A])) and 
               ([B] = "" or Text.Contains([@B], [B])) and 
               ([C] = "" or Text.Contains([@C], [C]))
       ),
       // 返回匹配到的第一个Value值，无匹配则返回0
       Result = if Table.RowCount(MatchRows) > 0 then MatchRows[Value]{0} else 0
   in
       Result
   

3. 输出结果
   点击「关闭并上载」，将结果加载回Excel。后续修改条件或原始数据后，只需点击「数据」→「全部刷新」即可更新结果。

核心优势 #

• 无需手动下拉公式，一次性处理所有行
• 条件修改后刷新即可生效，维护成本低
• 支持后续扩展更复杂的匹配逻辑

内容的提问来源于stack exchange，提问作者Dovahkiin