如何在Excel单元格含多值时匹配特定SubjectID并填充ValidQuestions

VLOOKUP失效的原因是它只能按查找值精确匹配首列的单个值，但你的第一个工作表SubjectID是单个/斜杠分隔的多值，需要反过来判断第二个表的单个ID是否包含在第一个表的ID字符串中，以下是两种可行方案：

方案1：适用于Excel 365/2021及以后版本 #

在第二个工作表的ValidQuestions列（假设从B2开始）输入公式：

=XLOOKUP(TRUE,ISNUMBER(SEARCH(A2,Sheet1!$A$2:$A$100)),Sheet1!$B$2:$B$100,"无匹配")

• 逻辑：SEARCH(A2, Sheet1!$A$2:$A$100)检查第二个表的单个ID（A2）是否存在于第一个表的SubjectID单元格中，ISNUMBER将结果转为布尔值，XLOOKUP找到第一个匹配的ValidQuestions值，最后一个参数是无匹配时的返回内容。
• 调整：把Sheet1换成你的第一个工作表名称，$A$2:$A$100和$B$2:$B$100替换为实际的数据范围。

方案2：兼容旧版Excel（2019及以前） #

使用数组公式，在第二个工作表B2输入：

=INDEX(Sheet1!$B$2:$B$100,MATCH(TRUE,ISNUMBER(SEARCH(A2,Sheet1!$A$2:$A$100)),0))

• 注意：输入完成后需要按Ctrl+Shift+Enter触发数组计算（Excel 365无需此操作）。

进阶：避免部分匹配 #

如果担心出现类似XXX000误匹配XXX0001的情况，可以给搜索前后加空格，确保匹配完整ID：

// 365版本
=XLOOKUP(TRUE,ISNUMBER(SEARCH(" "&A2&" "," "&Sheet1!$A$2:$A$100&" ")),Sheet1!$B$2:$B$100,"无匹配")

// 旧版本
=INDEX(Sheet1!$B$2:$B$100,MATCH(TRUE,ISNUMBER(SEARCH(" "&A2&" "," "&Sheet1!$A$2:$A$100&" ")),0))

原理是给每个SubjectID前后添加空格，确保匹配的是独立的ID片段（比如CCC111会变成CCC111，不会和CCC1111混淆）。

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