如何基于逗号分隔的查找值使用XLOOKUP函数

核心公式实现 #

假设你的数据结构为：

• A列：存储关联测试编号（单个或逗号分隔，如s1对应r1,r2）
• B列：对应房间号
• F列：需要查询的单个测试编号（如r1、s1）

在结果列（比如G2）输入以下公式，下拉即可批量获取对应房间号：

=XLOOKUP(TRUE, ISNUMBER(SEARCH(","&F2&",", ","&A:A&",")), B:B, "无匹配")

公式逻辑拆解 #

• ","&F2&","：给查询的单个测试编号前后加逗号，避免模糊匹配（比如防止r1被误匹配到r10）
• ","&A:A&","：给A列每个单元格的关联编号前后加逗号，统一格式
• SEARCH(...)：在处理后的A列内容中查找目标编号，找到返回位置，找不到返回错误值
• ISNUMBER(...)：将SEARCH的结果转换为布尔值（找到为TRUE，找不到为FALSE）
• XLOOKUP(TRUE, ...)：定位第一个TRUE对应的B列房间号，返回结果；无匹配时返回"无匹配"

特殊情况兼容 #

• 当A列是单个测试编号（如s1）时，公式依然有效，因为处理后会变成,s1,，和查询的,s1,完全匹配
• 单个s测量对应多个r/b测试的场景，只要目标r/b编号在A列的逗号分隔列表中，就能正确返回对应的房间号

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