如何正确使用XLOOKUP公式匹配多列IP并关联相关信息

通用兼容方案（所有Excel版本） #

使用INDEX+MATCH组合，支持精确匹配多列范围，兼容性覆盖所有Excel版本。

假设：

• 表格A的目标IP在单元格A2
• 表格B的IP/IP2/IP3列范围是B!$B:$D
• 表格B的OS列是B!$E:$E，Techno列是B!$F:$F，Comment列是B!$G:$G

提取OS信息 #

=IFERROR(INDEX(B!$E:$E, MATCH(A2, B!$B:$D, 0)), "未匹配")

提取Techno信息 #

=IFERROR(INDEX(B!$F:$F, MATCH(A2, B!$B:$D, 0)), "未匹配")

提取Comment信息 #

=IFERROR(INDEX(B!$G:$G, MATCH(A2, B!$B:$D, 0)), "未匹配")

原理说明：

• MATCH(A2, B!$B:$D, 0)会从左到右扫描表格B的IP/IP2/IP3列，找到第一个与A2精确匹配的单元格，返回其所在行号
• INDEX根据返回的行号，提取对应列的关联信息
• IFERROR用于处理无匹配的情况，返回自定义提示文本

Excel 365/2021专属简洁方案 #

利用动态数组函数简化公式，操作更高效：

方案1：嵌套XLOOKUP依次查找 #

# 提取OS
=XLOOKUP(A2, B!$B:$B, B!$E:$E, XLOOKUP(A2, B!$C:$C, B!$E:$E, XLOOKUP(A2, B!$D:$D, B!$E:$E, "未匹配")))

# 提取Techno
=XLOOKUP(A2, B!$B:$B, B!$F:$F, XLOOKUP(A2, B!$C:$C, B!$F:$F, XLOOKUP(A2, B!$D:$D, B!$F:$F, "未匹配")))

原理说明：依次在IP、IP2、IP3列中查找目标IP，前一列无匹配则自动查找下一列，最终返回对应信息或提示文本。

方案2：合并列后匹配 #

# 提取OS
=XLOOKUP(A2, TOCOL(B!$B:$D), TOCOL(CHOOSE({1,2,3}, B!$E:$E, B!$E:$E, B!$E:$E)), "未匹配")

原理说明：

• TOCOL(B!$B:$D)将表格B的三个IP列合并为单列数组
• CHOOSE({1,2,3}, B!$E:$E, B!$E:$E, B!$E:$E)将OS列重复三次，与合并后的IP列形成一一对应
• XLOOKUP直接在合并后的IP数组中匹配，返回对应OS信息

注意事项 #

• 若IP存在空格导致匹配失败，可在公式中加入TRIM处理，如将A2替换为TRIM(A2)，Excel 365版本可直接写MATCH(TRIM(A2), TRIM(B!$B:$D), 0)
• 若同一IP在表格B的多行出现，上述公式均返回第一个匹配结果；如需提取所有匹配信息，可使用FILTER函数（仅Excel 365支持）

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