Excel嵌套IF函数替代方案咨询：带通配符的文本匹配需求

下面提供几种更易维护的方案，解决嵌套公式臃肿的问题：

1. 辅助匹配表+INDEX+MATCH数组公式（兼容多数Excel版本） #

操作步骤： #

• 新建一个工作表（比如命名为匹配规则），设置两列：
  • A列：输入匹配规则（带通配符*，比如*APPLE.COM/BILL*、*IIA VOYA*、*VENMO PAYMENT*）
  • B列：输入对应要显示的结果（比如AP、VOYA、Venmo）
• 在需要显示结果的单元格输入公式：

  =INDEX(匹配规则!$B$2:$B$100,MATCH(TRUE,COUNTIF($F10,匹配规则!$A$2:$A$100)>0,0))
  

  • 旧版Excel（2019及以前）输入后需按Ctrl+Shift+Enter触发数组运算；新版Excel（365/2021）直接回车即可
  • 若需设置无匹配时的默认值，可套一层IFERROR：

    =IFERROR(INDEX(匹配规则!$B$2:$B$100,MATCH(TRUE,COUNTIF($F10,匹配规则!$A$2:$A$100)>0,0)),"未匹配")
    

优点： #

• 新增/修改规则只需在匹配表中编辑，无需修改公式
• 兼容绝大多数Excel版本

2. 辅助匹配表+XLOOKUP（Excel 365/2021专属） #

如果使用Excel 365或2021，XLOOKUP的通配符支持可以简化公式：

=XLOOKUP(TRUE,COUNTIF($F10,匹配规则!$A$2:$A$100)>0,匹配规则!$B$2:$B$100,"未匹配")

优点： #

• 语法更简洁，无需数组运算触发
• 自带默认值参数，无需额外嵌套IFERROR

3. VBA自定义函数（灵活度最高） #

若需要更复杂的匹配逻辑（比如正则匹配），可以编写自定义函数：

操作步骤： #

1. 按Alt+F11打开VBA编辑器
2. 插入一个新模块，粘贴以下代码：

Function GetCategory(sourceCell As Range, ruleRange As Range) As String
    Dim ruleRow As Range
    ' 遍历匹配规则表的每一行
    For Each ruleRow In ruleRange.Rows
        ' 检查源单元格是否匹配当前规则（Like支持通配符*）
        If sourceCell.Value Like ruleRow.Cells(1, 1).Value Then
            GetCategory = ruleRow.Cells(1, 2).Value
            Exit Function ' 找到第一个匹配项后退出
        End If
    Next ruleRow
    ' 无匹配时返回默认值
    GetCategory = "未匹配"
End Function

3. 返回Excel工作表，在目标单元格输入公式：

=GetCategory(F10,匹配规则!$A$2:$B$100)

优点： #

• 支持更复杂的匹配逻辑（可扩展正则表达式）
• 公式调用简单，规则维护集中在匹配表

4. Power Query批量处理（适合大数据量） #

如果需要批量处理整列数据，Power Query是更高效的选择：

操作步骤： #

1. 选中源数据列（比如F列），点击数据选项卡→从表格/区域（Excel 365）或自表格（旧版）
2. 在Power Query编辑器中，点击添加列→条件列
3. 依次添加匹配规则：
   • 条件：文本包含→输入APPLE.COM/BILL，输出AP
   • 继续添加其他规则（IIA VOYA→VOYA，VENMO PAYMENT→Venmo等）
   • 设置默认值为未匹配
4. 点击关闭并上载，将处理后的数据加载回Excel

优点： #

• 规则可视化编辑，新增/修改更直观
• 支持批量更新，源数据变化后只需刷新即可

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