Excel VLOOKUP如何对单元格内多逗号分隔查找值返回同格式结果

你之前参考的两个方案核心为「单个查找值匹配多行返回多结果后拼接」，与当前「单个单元格内多查找值逐个匹配单结果后拼接」的场景逻辑相反，直接套用会不兼容，以下是适配不同Excel版本的可直接使用的实现方法：

Excel 365/2021及以上（支持动态数组）最简实现 #

基础表结构假设：

• 查找源表：颜色列位于A2:A5，对应键值列位于B2:B5
• 待匹配颜色值存放在D2单元格，匹配结果输出单元格为E2

在E2单元格输入以下公式，直接按回车即可计算，下拉可批量套用：

=LET(
    待匹配内容, D2,
    源颜色范围, $A$2:$A$5,
    源键值范围, $B$2:$B$5,
    拼接分隔符, IF(ISNUMBER(FIND("、",待匹配内容)),"、",","),
    拆分独立值, TEXTSPLIT(待匹配内容,拼接分隔符),
    逐值匹配结果, XLOOKUP(拆分独立值,源颜色范围,源键值范围,"无匹配"),
    TEXTJOIN(拼接分隔符,TRUE,逐值匹配结果)
)

该公式完全匹配预期输出效果：

• 自动识别原单元格使用全角顿号或半角逗号作为分隔符，匹配结果使用与原输入完全一致的分隔符拼接
• 逐值拆分匹配，不受长文本格式影响
• 源表未找到的颜色值默认返回“无匹配”，可自行替换为空文本或其他提示内容

Excel 2019及更早版本（无动态数组支持）实现 #

若Excel版本不支持TEXTSPLIT、XLOOKUP函数，基于上述相同表结构，在E2单元格输入以下公式，输入完成后按Ctrl+Shift+回车锁定数组计算，再下拉批量套用：

=TEXTJOIN(IF(ISNUMBER(FIND("、",D2)),"、",","),TRUE,
    IFERROR(
        VLOOKUP(
            TRIM(
                MID(
                    SUBSTITUTE(D2,IF(ISNUMBER(FIND("、",D2)),"、",","),REPT(" ",99)),
                    ROW(INDIRECT("1:"&LEN(D2)-LEN(SUBSTITUTE(D2,IF(ISNUMBER(FIND("、",D2)),"、",","),""))+1))*99-98,
                    99
                )
            ),
            $A$2:$B$5,
            2,
            0
        ),
        "无匹配"
    )
)

注意事项 #

• 公式中$A$2:$B$5为绝对引用的源匹配范围，根据实际表格位置调整即可，下拉计算时不会发生偏移
• 若待匹配单元格内混合使用顿号和半角逗号（例如内容为“红、黄,绿”），可将拆分步骤的分隔符参数同时加入两类分隔符，公式会自动识别混合格式完成拆分
• 拆分过程会自动清除独立值前后的多余空格，避免因输入空格导致匹配失败

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