Excel表格用列号替代地址实现XLOOKUP查找右侧非空单元格表头

问题背景 #

需要定位当前单元格右侧首个有内容的单元格，并返回其对应的表头值。目前已通过硬编码列地址实现需求，公式如下：

=XLOOKUP(TRUE,TBL_Plan[[#Headers],[2023-12-04]:[2024-01-15]]<>"",TBL_Plan[@[2023-12-04]:[2024-01-15]],,,1)

但表头名称可能被他人修改，硬编码的列地址无法适配这种变化。已知可通过COLUMN()获取当前单元格的工作表列号，COLUMNS(TBL_Plan)获取表格的总列数，但尝试直接用列号范围定义表数组（如TBL_Plan[[#Headers],Column()+1:columns(TBL_Plan)]）无法生效，需要动态构建表区域的方法。

解决方案 #

Excel的结构化引用不支持直接用列号范围（如Column()+1:COLUMNS(TBL_Plan)），需借助INDEX函数动态定位区域的起止列，再结合XLOOKUP实现需求。

最终公式 #

=XLOOKUP(TRUE, 
    INDEX(TBL_Plan[#Headers],,COLUMN()-COLUMN(TBL_Plan[#Headers])+2):INDEX(TBL_Plan[#Headers],,COLUMNS(TBL_Plan))<>"",
    INDEX(TBL_Plan[@],,COLUMN()-COLUMN(TBL_Plan[#Headers])+2):INDEX(TBL_Plan[@],,COLUMNS(TBL_Plan)),
    ,,1)

公式说明 #

1. 计算当前单元格在表内的列索引：COLUMN()-COLUMN(TBL_Plan[#Headers])+1，将工作表列号转换为表格内的相对列序号（比如表格从B列开始，当前单元格在D列，计算结果为3）。
2. 动态构建表头区域：
   • INDEX(TBL_Plan[#Headers],,当前列索引+1)：定位到当前单元格右侧第一列的表头
   • INDEX(TBL_Plan[#Headers],,COLUMNS(TBL_Plan))：定位到表格最后一列的表头
   • 两者组合成从当前列右侧到表尾的表头区域
3. 动态构建当前行数据区域：逻辑与表头区域一致，只是引用对象换成TBL_Plan[@]（当前行）
4. XLOOKUP参数：最后一个参数1表示按从左到右的顺序查找第一个非空单元格（匹配首个TRUE结果）

简化版（用LET函数提升可读性） #

如果使用支持LET函数的Excel版本（365/2021及以上），可以将重复计算的部分提取出来，让公式更易读：

=LET(
    CurrentTableCol, COLUMN()-COLUMN(TBL_Plan[#Headers])+1,
    StartCol, CurrentTableCol+1,
    EndCol, COLUMNS(TBL_Plan),
    HeaderRange, INDEX(TBL_Plan[#Headers],,StartCol):INDEX(TBL_Plan[#Headers],,EndCol),
    DataRange, INDEX(TBL_Plan[@],,StartCol):INDEX(TBL_Plan[@],,EndCol),
    XLOOKUP(TRUE, HeaderRange<>"", DataRange,,,-1)
)

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