如何在Excel中将逗号分隔索引匹配对应值并拼接？

一、Excel 365/2021及以上版本（支持动态数组函数） #

直接用TEXTSPLIT+XLOOKUP+TEXTJOIN组合一步完成。假设：

• 待转换的逗号分隔索引在Sheet1的A列（如A2为44,26）
• 参考数组在Sheet2：A列为索引（Index），B列为对应属性（Color）

公式： #

=TEXTJOIN(",",TRUE,XLOOKUP(TEXTSPLIT(A2,","),Sheet2!$A:$A,Sheet2!$B:$B,"",0))

各部分作用： #

• TEXTSPLIT(A2,",")：将单元格内的索引字符串按逗号拆分为单个索引的动态数组
• XLOOKUP(...)：遍历拆分后的每个索引，在参考表中匹配对应属性；第四个参数""表示找不到时返回空值，最后一个0表示精确匹配
• TEXTJOIN(",",TRUE,...)：用逗号拼接所有匹配结果，TRUE参数会自动忽略空值（即跳过无匹配的索引）

示例效果：

• A2=44,26 → 返回blue,yellow
• A4=35,45,21 → 返回silver,orange（忽略无匹配的21）
• 若需保留无匹配索引的空位置，将TEXTJOIN的第二个参数改为FALSE，结果会变成silver,orange,

二、旧版Excel（2019及更早，无动态数组函数） #

方法1：数组公式+TEXTJOIN+VLOOKUP #

若你的Excel版本支持TEXTJOIN（2019版及部分订阅版），可使用以下数组公式（输入后需按Ctrl+Shift+Enter确认）：

=TEXTJOIN(",",TRUE,IFERROR(VLOOKUP(--TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99)),Sheet2!$A$2:$B$7,2,0),""))

方法2：辅助列拆分法（更易维护） #

如果数组公式过于复杂，可以用辅助列分步处理：

1. 拆分索引：在Sheet1的B2单元格输入公式，向右拖动直到出现空值：

   =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),(COLUMN(A:A)-1)*99+1,99))
   

   该公式会把A2中的每个索引拆分到B、C、D等列
2. 拼接属性：在最后一个辅助列右侧单元格输入公式，将各列的匹配结果拼接：

   =TEXTJOIN(",",TRUE,IFERROR(VLOOKUP(--B2,Sheet2!$A:$A,Sheet2!$B:$B,0),""),IFERROR(VLOOKUP(--C2,Sheet2!$A:$A,Sheet2!$B:$B,0),""),IFERROR(VLOOKUP(--D2,Sheet2!$A:$A,Sheet2!$B:$B,0),"")))
   

方法3：自定义VBA函数 #

若允许使用VBA，可自定义函数实现更灵活的转换：

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

   Function IndexToAttr(indexStr As String, refRange As Range) As String
       Dim arr() As String
       Dim result As String
       Dim i As Integer
       Dim cell As Range
       
       arr = Split(indexStr, ",")
       result = ""
       
       For i = LBound(arr) To UBound(arr)
           For Each cell In refRange.Columns(1).Cells
               If Trim(arr(i)) = Trim(cell.Value) Then
                   result = result & cell.Offset(0, 1).Value & ","
                   Exit For
               End If
           Next cell
       Next i
       
       IndexToAttr = IIf(Len(result) > 0, Left(result, Len(result) - 1), "")
   End Function
   

2. 返回Excel，在目标单元格输入公式：

   =IndexToAttr(A2,Sheet2!$A$2:$B$7)
   

内容的提问来源于stack exchange，提问作者Faye D.