如何在Excel中用SUMPRODUCT等函数匹配拆分字符串对应成本

针对你遇到的分号分隔多值的匹配问题，以下是两种适用的Excel函数方案：

方案1：适用于Excel 365/2021（支持动态数组） #

使用TEXTSPLIT拆分原表的多值数据，再用XLOOKUP匹配对应成本：
在目标表的B3单元格（对应"aaa"）输入公式：

=XLOOKUP(A3, TEXTSPLIT(表1!$A$2:$A$3, ";"), TEXTSPLIT(表1!$B$2:$B$3, ";"), "未找到")

• TEXTSPLIT(表1!$A$2:$A$3, ";")：将原表所有Code按分号拆分为单个值的数组
• TEXTSPLIT(表1!$B$2:$B$3, ";")：同步拆分对应Cost为数组
• XLOOKUP直接匹配单个Code与Cost的对应关系，下拉单元格可自动填充所有结果

方案2：兼容旧版Excel（无动态数组功能） #

使用INDEX+MATCH+SEARCH组合定位匹配，需按Ctrl+Shift+Enter作为数组公式输入：

=INDEX(表1!$B$2:$B$3, MATCH(TRUE, ISNUMBER(SEARCH(";"&A3&";", ";"&表1!$A$2:$A$3&";")), 0))

若要避免Code包含子串导致的误匹配，可使用精准定位的公式提取对应位置的Cost：

=TRIM(MID(SUBSTITUTE(INDEX(表1!$B$2:$B$3, MATCH(TRUE, ISNUMBER(SEARCH(A3, 表1!$A$2:$A$3)), 0)), ";", REPT(" ", 100)), (FIND(A3, INDEX(表1!$A$2:$A$3, MATCH(TRUE, ISNUMBER(SEARCH(A3, 表1!$A$2:$A$3)), 0)))-LEN(SUBSTITUTE(LEFT(INDEX(表1!$A$2:$A$3, MATCH(TRUE, ISNUMBER(SEARCH(A3, 表1!$A$2:$A$3)), 0)), FIND(A3, INDEX(表1!$A$2:$A$3, MATCH(TRUE, ISNUMBER(SEARCH(A3, 表1!$A$2:$A$3)), 0))-1), ";", "")))*100+1, 100))

该公式逻辑：先定位包含目标Code的原表行，再根据Code在该行的顺序位置，提取对应位置的Cost值。

为什么SUMPRODUCT无法匹配多值？ #

SUMPRODUCT主要用于数组的乘积求和运算，无法直接建立分号分隔多值的位置对应关系，因此只能匹配单个独立的Code（如"ccc"），无法处理拆分后的单个子Code。

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