如何在Excel数据模型中实现带通配符的INDEX/MATCH查找

该方案完全复现带通配符、按优先级取首个匹配结果的INDEX-MATCH逻辑，支持直接引用模型内RELATED关联字段，适配百万行级数据量，无需导出到普通工作表。

• 预处理查找表
  先给tblLookUp新增整数类型的Priority列，数值越小代表匹配优先级越高：比如示例中Model=A1的规则优先级最高设为1，SalesMan=Mario的规则设为2，Country=US的规则设为3，默认兜底规则设为999。该设计替代原生INDEX-MATCH对表物理行序的依赖，后续新增/调整规则只需修改优先级数值，无需拖拽调整行位置，稳定性更高。
• 新建计算列
  在tblFact中新建如下计算列，其中CurrentLUValue部分可直接拼接模型内任意字段（包括RELATED获取的关联表字段）：

  Matched Message =
  VAR CurrentLUValue = [LUValue] // 替换为实际的待匹配值逻辑，可直接使用RELATED关联字段拼接
  VAR MatchedRule =
      TOPN (
          1,
          FILTER (
              tblLookUp,
              SEARCH ( tblLookUp[LUKey], CurrentLUValue, 1, 0 ) > 0
          ),
          tblLookUp[Priority], ASC
      )
  RETURN
      MAXX ( MatchedRule, tblLookUp[Message] )
  

公式说明 #

• SEARCH函数原生支持Excel标准通配符（*匹配任意长度字符、?匹配单个字符、~用于转义通配符本身），匹配规则和原生INDEX-MATCH完全一致，第四个参数设为0用于兜底匹配失败的场景，避免报错。
• TOPN按预先设置的优先级升序筛选，仅返回优先级最高的1条匹配规则，完全对齐原公式取首个命中结果的逻辑。
• 最后用MAXX从单条结果的临时表中提取Message标量值，避免行上下文转换报错。

性能说明 #

针对百万行事实表、百条级匹配规则的场景，该计算列刷新无明显性能瓶颈，远快于普通工作表数组公式。如果匹配规则超过千条，可考虑使用下文的Power Query方案。

如果需要在数据加载阶段就完成匹配（避免计算列占用模型内存），可直接在Power Query中实现通配符匹配，无需依赖合并查询功能：

• 提前在Power Query中给tblLookUp添加Priority优先级列，和DAX方案规则一致。
• 在tblFact查询中新增自定义列，输入如下M公式：

  = let
      CurrentMatchValue = [LUValue], // 替换为实际待匹配值，可直接引用PQ中合并的关联表字段
      FilteredRules = Table.SelectRows(
          tblLookUp,
          each Value.Is(Text.Match(CurrentMatchValue, [LUKey]), type text)
      ),
      TopPriorityRule = Table.Min(FilteredRules, "Priority")
  in
      TopPriorityRule[Message]? ?? "Default message"
  

  其中Text.Match原生支持Excel标准通配符规则，??用于兜底无匹配规则的场景，返回默认提示。

• 之前使用FILTER+SEARCH组合报错，通常是因为未给SEARCH传入第四个兜底参数，导致匹配不到值时返回错误中断计算。
• LOOKUPVALUE本身仅支持精确匹配，不支持通配符和优先级逻辑，不适用于该场景。

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