如何在Excel 2019中实现双数据区域的精确与前缀部分匹配？

核心逻辑 #

按优先级执行匹配：

1. 数据库1精确匹配
2. 数据库1最长公共前缀匹配（前缀长度≥4）
3. 数据库2精确匹配
4. 数据库2最长公共前缀匹配（前缀长度≥4）
5. 以上均无匹配时返回NOT FOUND

完整公式（需按Ctrl+Shift+Enter触发数组计算） #

将公式放在目标单元格（如H2），下拉应用到所有行：

=IFERROR(
    -- 数据库1精确匹配
    INDEX($A$3:$A$13,MATCH(G2,$B$3:$B$13,0)),
    IFERROR(
        -- 数据库1最长前缀匹配（≥4位）
        INDEX($A$3:$A$13,
            SUMPRODUCT(
                (MAX(IF(LEFT(G2,ROW($1:$99))=LEFT($B$3:$B$13,ROW($1:$99)),ROW($1:$99),0))=IF(LEFT(G2,ROW($1:$99))=LEFT($B$3:$B$13,ROW($1:$99)),ROW($1:$99),0))*
                (MAX(IF(LEFT(G2,ROW($1:$99))=LEFT($B$3:$B$13,ROW($1:$99)),ROW($1:$99),0))>=4)*
                ROW($B$3:$B$13)
            )-ROW($B$2)
        ),
        IFERROR(
            -- 数据库2精确匹配
            INDEX($D$3:$D$30,MATCH(G2,$E$3:$E$30,0)),
            IFERROR(
                -- 数据库2最长前缀匹配（≥4位）
                INDEX($D$3:$D$30,
                    SUMPRODUCT(
                        (MAX(IF(LEFT(G2,ROW($1:$99))=LEFT($E$3:$E$30,ROW($1:$99)),ROW($1:$99),0))=IF(LEFT(G2,ROW($1:$99))=LEFT($E$3:$E$30,ROW($1:$99)),ROW($1:$99),0))*
                        (MAX(IF(LEFT(G2,ROW($1:$99))=LEFT($E$3:$E$30,ROW($1:$99)),ROW($1:$99),0))>=4)*
                        ROW($E$3:$E$30)
                    )-ROW($E$2)
                ),
                "NOT FOUND"
            )
        )
    )
)

公式各部分说明 #

1. 精确匹配模块：沿用你原有的INDEX+MATCH逻辑，直接匹配完全一致的条目
2. 最长前缀计算：
   • 用ROW($1:$99)遍历1-99位前缀长度（覆盖绝大多数数据场景）
   • 对比目标值与数据库条目的前缀，取最大匹配长度
3. 前缀筛选与定位：
   • 筛选出最大前缀长度≥4的条目
   • 通过SUMPRODUCT计算符合条件的条目行号，转换为INDEX的相对位置
4. 多层IFERROR嵌套：按优先级依次尝试匹配，全部失败则返回NOT FOUND

关键注意事项 #

• 数组公式触发：Excel 2019无动态数组支持，输入公式后必须按Ctrl+Shift+Enter生效
• 范围调整：根据实际数据修改公式中的数据库范围（如$A$3:$A$13、$B$3:$B$13等）
• 格式统一：若目标值或数据库条目为数值/特殊格式，可添加TEXT函数统一格式，例如将G2替换为TEXT(G2,"0")
• 多重复匹配：若多个条目有相同的最长前缀，公式会返回第一个匹配的条目

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