Excel如何在全矩阵搜索指定日期并返回其相邻的属性值

方法1：公式实现（兼容全版本Excel） #

核心用INDEX+MATCH组合实现多列范围搜索，假设原始数据区域为A2:F100（A列是User，B列Date0、C列property0、D列Date1、E列property1，按规律依次排列），目标表格中当前行的用户存放在H列，待匹配的目标日期存放在I列，匹配对应property的公式如下：

=IFERROR(INDEX($A:$F, MATCH(H2,$A:$A,0), MATCH(I2,INDIRECT(ROW(MATCH(H2,$A:$A,0))&":"&ROW(MATCH(H2,$A:$A,0))),0)+1), "")

逻辑说明：

• 内层MATCH(H2,$A:$A,0)先定位到当前用户对应的行号
• INDIRECT提取当前用户整行的所有数据区域
• 第二层MATCH在当前用户行的所有单元格中搜索目标日期，返回日期所在的列号
• 列号+1即为对应property的列位置，INDEX直接取值，匹配失败则返回空值

如果你使用的是365/2021及以上版本，可以用XLOOKUP简化写法：

=XLOOKUP(I2, FILTER(B:F, A:A=H2), FILTER(C:G, A:A=H2), "")

方法2：Power Query批量重构表格（适合大数据量场景） #

这种宽表转结构化表的需求用Power Query处理效率更高，不用手动写公式，操作步骤：

• 选中原始数据区域，点击「数据」选项卡→「从表格/区域」导入Power Query编辑器
• 选中User列，右键点击「逆透视其他列」，把分散的多列日期、属性统一转为行存储
• 新增自定义列，标记值类型：如果列名包含Date则标记为「日期」，包含property则标记为「属性」
• 再新增自定义列，提取分组序号：提取列名末尾的数字（比如Date0提取0、property1提取1），用来配对同组的日期和属性
• 选中刚才创建的「值类型」列，右键点击「透视列」，值字段选择原始的数值列
• 按User、分组序号排序后，按需求重命名列名即可导出回Excel

注意事项 #

• 所有日期列的格式要统一设置为日期格式，避免文本型日期和数值型日期匹配失败
• 如果原始表的Date列和property列是严格交替排列的，上述公式的偏移量逻辑不用调整，如果中间夹杂其他列需要对应修改列范围

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