如何在单个单元格中基于多条件查找多值并返回至同一单元格？

前置说明 #

假设：

• 主表productNameEN列的多值以逗号+空格分隔（如Onion, Red onion）
• 翻译表的英文列（EN）与冰岛语列（IS）为一对一映射，示例中位于Sheet2的A2:A6和B2:B6区域（可根据实际修改）

1. Excel 365/2021及以上版本（动态数组支持） #

利用TEXTJOIN、FILTERXML和XLOOKUP（或INDEX+MATCH）实现批量匹配与合并：

在productNameIS列的首个单元格（如C2）输入公式：

=TEXTJOIN(", ", TRUE, XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(B2, ", ", "</s><s>")&"</s></t>", "//s"), Sheet2!$A$2:$A$6, Sheet2!$B$2:$B$6, ""))

公式拆解： #

• SUBSTITUTE(B2, ", ", "</s><s>")：将多值分隔符替换为XML节点标签，为拆分做准备
• FILTERXML(...)：通过XML语法将单单元格多值拆分为独立的动态数组元素
• XLOOKUP(...)：对数组内每个英文值批量匹配对应冰岛语翻译
• TEXTJOIN(", ", TRUE, ...)：将翻译结果用逗号空格合并为单个单元格，自动忽略空值

若偏好INDEX+MATCH组合，可替换为：

=TEXTJOIN(", ", TRUE, INDEX(Sheet2!$B$2:$B$6, MATCH(FILTERXML("<t><s>"&SUBSTITUTE(B2, ", ", "</s><s>")&"</s></t>", "//s"), Sheet2!$A$2:$A$6, 0)))

2. Google Sheets #

借助SPLIT、ARRAYFORMULA和TEXTJOIN完成需求：

在productNameIS列首个单元格（如C2）输入：

=TEXTJOIN(", ", TRUE, ARRAYFORMULA(XLOOKUP(SPLIT(B2, ", "), Sheet2!$A$2:$A$6, Sheet2!$B$2:$B$6, "")))

公式拆解： #

• SPLIT(B2, ", ")：直接按逗号空格拆分多值为数组
• ARRAYFORMULA(XLOOKUP(...))：批量处理数组内每个值的翻译匹配
• TEXTJOIN(...)：合并翻译结果为单个单元格

改用INDEX+MATCH的版本：

=TEXTJOIN(", ", TRUE, ARRAYFORMULA(INDEX(Sheet2!$B$2:$B$6, MATCH(SPLIT(B2, ", "), Sheet2!$A$2:$A$6, 0))))

3. 旧版Excel（无动态数组，如2019及更早） #

通过VBA自定义函数实现：

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

Function TranslateAndJoin(enText As String, enRange As Range, isRange As Range) As String
    Dim arr() As String
    Dim i As Integer
    Dim result As String
    arr = Split(enText, ", ")
    
    For i = LBound(arr) To UBound(arr)
        Dim matchRow As Integer
        On Error Resume Next
        matchRow = Application.Match(arr(i), enRange, 0)
        On Error GoTo 0
        If matchRow > 0 Then
            result = IIf(result <> "", result & ", ", "") & isRange.Cells(matchRow).Value
        End If
    Next i
    
    TranslateAndJoin = result
End Function

2. 返回工作表，在C2单元格输入：

=TranslateAndJoin(B2, Sheet2!$A$2:$A$6, Sheet2!$B$2:$B$6)

3. 下拉填充公式即可完成批量处理

效果验证 #

针对你的测试数据：

• 主表B2单元格Onion, Red onion会返回Laukur, Rauðlaukur
• B3单元格Egg, Egg yolk会返回Egg, Eggjarauða
• B4单元格Lemon会返回Sítronu
  完全符合预期需求

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