Excel多列ID匹配查询：大数据集下的取值方案

初始数据表格 #

拆分后的多列展示形式 #

待填充的目标表格 #

期望得到的结果表格 #

问题描述 #

数据集共83900行，若将初始表拆分为多列会产生2000列，尝试过INDEX+MATCH公式但因数据量过大无法生效，考虑使用SEARCH函数但未找到可行方案，求适合的填充公式。

解决方案 #

针对大数据量场景，推荐以下几种高效公式：

1. 适用于Excel 365/2021及以上版本（动态数组，批量填充）

假设目标表格的ID列在A2:A5，初始表的Values列在初始表!A:A，IDs列在初始表!B:B，在目标表格的B2单元格输入：

=MAP(A2:A5, LAMBDA(id, XLOOKUP("*,"&id&",*", ","&初始表!B:B&",", 初始表!A:A, "")))

公式说明：

• 用逗号包裹ID和初始表的IDs内容，避免部分匹配（比如ID=1不会误匹配159中的1）
• MAP函数批量处理每个ID，XLOOKUP快速定位对应行的Values

2. 适用于全版本Excel（高效非数组公式）

在目标表格的B2单元格输入，然后下拉填充：

=INDEX(初始表!$A$2:$A$83901, AGGREGATE(15, 6, (ROW(初始表!$A$2:$A$83901)-ROW(初始表!$A$1))/ISNUMBER(SEARCH(","&A2&",", ","&初始表!$B$2:$B$83901&",")), 1))

公式说明：

• AGGREGATE函数忽略错误值，比传统数组公式更高效，避免大数据量下卡顿
• SEARCH配合逗号包裹的匹配条件，确保精准匹配单个ID

3. 简化版（Excel 365/2021）

如果能保证ID不会出现在其他ID的子串中（比如没有ID=1和ID=159的情况），可以用更简洁的公式：

=XLOOKUP("*"&A2&"*", 初始表!$B$2:$B$83901, 初始表!$A$2:$A$83901, "")

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