如何用XLOOKUP查找单元格中多IP对应的首个匹配值？

核心公式（适用于Excel 365/2021及支持动态数组的版本） #

假设存储IP与时间戳的工作表名为IP记录，其中IP在A列，时间戳在B列，直接在目标单元格输入以下公式：

=XLOOKUP(TRUE,ISNUMBER(XMATCH(TEXTSPLIT(A1,", ",""),'IP记录'!A:A)),'IP记录'!B:B,"无匹配")

公式拆解 #

• TEXTSPLIT(A1,", ","")：把A1中用逗号+空格分隔的多个IP拆分成独立的IP数组
• XMATCH(TEXTSPLIT(...), 'IP记录'!A:A)：逐个检查拆分后的IP是否存在于目标工作表的IP列，存在则返回对应行号，不存在返回错误值
• ISNUMBER(...)：将XMATCH的结果转换为布尔值数组（存在为TRUE，不存在为FALSE）
• XLOOKUP(TRUE, ..., 'IP记录'!B:B)：定位数组中第一个TRUE的位置，返回对应行的时间戳
• 最后一个参数"无匹配"是未找到任何IP时的返回内容，可根据需求修改

兼容旧版Excel（无TEXTSPLIT的版本） #

如果你的Excel版本不支持TEXTSPLIT，用FILTERXML替代拆分逻辑，公式如下：

=XLOOKUP(TRUE,ISNUMBER(XMATCH(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","</s><s>")&"</s></t>","//s"),'IP记录'!A:A)),'IP记录'!B:B,"无匹配")

额外注意事项 #

• 如果目标IP列存在多余空格，会导致匹配失败，可将'IP记录'!A:A替换为TRIM('IP记录'!A:A)来去除空格，确保匹配准确
• 若需要返回最后一个匹配的时间戳，可将XLOOKUP的第5个参数设为-1（默认是1，查找第一个匹配项）

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