Excel多条件模糊匹配：匹配Lookup列返回Category及公式异常排查

需求说明 #

实现逻辑：当单元格文本包含lookup_table结构化表中Lookup1列的内容时，优先匹配同时包含对应Lookup2列内容的记录，返回其Category1分类；若仅匹配Lookup1，则返回该Lookup1对应Lookup2为空的分类。结构化表新增记录时，公式需自动扩展引用范围。

目标单元格示例 #

abc 2021 Gross Profit abcd
ab 2022 Gross Profit ADJ abcde
ab ADJ 2021 Gross Profit abcde
cd 2023 Payroll asdf
dage Sales 2021 bce
2020 Payroll Revision abcdef

预期分类输出 #

对应依次为：Actual、Adjustment、Adjustment、Payroll、Sales、Payroll Adjustment

lookup_table 结构化表 #

当前问题 #

用户使用以下公式时，返回双值数组且无法得到正确分类：

=IFERROR(FILTER(IFERROR(
    LET(cell,M3,
        lookup1_range,lookup_table[Lookup1],
        lookup2_range,lookup_table[Lookup2],
        category_range,lookup_table[Category1],
        contains_both,IF(AND(ISNUMBER(FIND(lookup1_range,cell)), ISNUMBER(FIND(lookup2_range,cell))), 1, 0),
        contains_lookup1,IF(ISNUMBER(FIND(lookup1_range,cell)),1,0),
        matches_lookup1,IF(contains_lookup1,MATCH(lookup1_range,lookup1_range,0),""),
        matches_both,IF(contains_both,IF(ISNUMBER(FIND(lookup2_range,INDEX(lookup1_range,matches_lookup1))),matches_lookup1,""),""),
        matches,IF(matches_both<>
        lookup_value,IF(matches_both<>
        IF(lookup_value<>
            lookup_value,
            IF(matches_lookup1<>
                IF(ISNUMBER(FIND(lookup2_range,cell)),
                    INDEX(category_range, matches_lookup1, 1),
                    INDEX(category_range, matches_lookup1, 1)),
                ""
            )
        )
    ),
    ""
),IFERROR(
    LET(cell,M3,
        lookup1_range,lookup_table[Lookup1],
        lookup2_range,lookup_table[Lookup2],
        category_range,lookup_table[Category1],
        contains_both,IF(AND(ISNUMBER(FIND(lookup1_range,cell)), ISNUMBER(FIND(lookup2_range,cell))), 1, 0),
        contains_lookup1,IF(ISNUMBER(FIND(lookup1_range,cell)),1,0),
        matches_lookup1,IF(contains_lookup1,MATCH(lookup1_range,lookup1_range,0),""),
        matches_both,IF(contains_both,IF(ISNUMBER(FIND(lookup2_range,INDEX(lookup1_range,matches_lookup1))),matches_lookup1,""),""),
        matches,IF(matches_both<>
        lookup_value,IF(matches_both<>
        IF(lookup_value<>
            lookup_value,
            IF(matches_lookup1<>
                IF(ISNUMBER(FIND(lookup2_range,cell)),
                    INDEX(category_range, matches_lookup1, 1),
                    INDEX(category_range, matches_lookup1, 1)),
                ""
            )
        )
    ),
    ""
)<>""),"")

解决方案 #

正确公式 #

将以下公式输入目标单元格（示例为M3），下拉即可自动应用到其他行：

=LET(
    cell, M3,
    lookup_data, lookup_table,
    lookup1, lookup_data[Lookup1],
    lookup2, lookup_data[Lookup2],
    category, lookup_data[Category1],
    -- 计算匹配得分：精确匹配（Lookup1+非空Lookup2）得2分，仅匹配Lookup1得1分，不匹配得0分
    match_score, ISNUMBER(FIND(lookup1, cell)) * (1 + (ISNUMBER(FIND(lookup2, cell)) * (lookup2 <> ""))),
    -- 取最高得分对应的分类，无匹配时返回"无匹配"可自行修改
    result, XLOOKUP(MAX(match_score), match_score, category, "无匹配", 0, 1)
)

公式逻辑解释 #

1. LET函数：定义变量简化公式结构，避免重复计算，提升可读性。
2. match_score 得分计算：
   • 若单元格不包含对应Lookup1内容，得分为0；
   • 若仅包含Lookup1且对应Lookup2为空，得分为1；
   • 若同时包含Lookup1和对应的非空Lookup2内容，得分为2（确保优先匹配更精确的记录）。
3. XLOOKUP函数：查找最高得分对应的分类，确保优先返回精确匹配的结果；当无任何匹配时，返回自定义文本（示例为"无匹配"，可按需修改）。

验证结果 #

应用公式后，示例单元格返回结果完全符合预期：

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