列错位时实现INDEX MATCH功能的XLOOKUP函数改写需求

问题背景 #

Sheet1与Sheet2均包含Shop Name、Shop status、Business Type、City、Operating hours、Delivery model6列。原INDEX MATCH函数=INDEX(Sheet2!$A$1:$H,MATCH($A2,Sheet2!$A$2:$A,0),MATCH(B$1,Sheet2!$A$1:$H$1,0))可通过表头匹配动态定位列，即使Sheet2中Shop status列位置变动仍能返回正确值；但原XLOOKUP函数=XLOOKUP($A2:A,Sheet2!$A$2:$A,Sheet2!$B$2:$B)因硬编码列范围，列位置变动后会失效。

解决方案 #

修改XLOOKUP函数，通过MATCH动态获取目标列范围，实现与INDEX MATCH相同的列错位适配能力：

=XLOOKUP($A2, Sheet2!$A$2:$A, INDEX(Sheet2!$A:$H, 0, MATCH(B$1, Sheet2!$A$1:$H$1, 0)))

公式解析 #

• MATCH(B$1, Sheet2!$A$1:$H$1, 0)：定位Sheet2中与Sheet1当前表头（如B$1对应的Shop status）匹配的列索引。
• INDEX(Sheet2!$A:$H, 0, [MATCH结果])：返回Sheet2中对应列的全部数据范围（0表示取整列）。
• XLOOKUP($A2, Sheet2!$A$2:$A, [动态列范围])：基于动态获取的列范围执行查找，确保列位置变动时仍能匹配正确数据。

使用说明 #

将此公式输入Sheet1的B2单元格后，可横向或纵向拖拽填充，适配所有列的查找需求——只要Sheet1与Sheet2的表头名称一致，无论Sheet2的列如何调整位置，均能返回正确匹配值。

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