自定义XLOOKUP公式报#VALUE!错误，求可行解决方案

问题概述 #

需通过单元格AY1中的列标题，自动匹配IMPORT BASE工作表中的对应列，构建XLOOKUP公式，预期生成类似=XLOOKUP(C2,'IMPORT BASE'!A:A,'IMPORT BASE'!CL:CL)的结果，但当前使用的公式返回#VALUE!错误。

当前错误公式 #

=XLOOKUP(C2,'IMPORT BASE'!A:A,CONCAT("'",TEXTBEFORE(TEXTAFTER(CELL("address",INDEX('IMPORT BASE'!$A$1:$DT$1,MATCH(AY1,'IMPORT BASE'!$A$1:$DT$1,0))),"]"),"$"),TEXTAFTER(TEXTBEFORE(TEXTAFTER(CELL("address",INDEX('IMPORT BASE'!$A$1:$DT$1,MATCH(AY1,'IMPORT BASE'!$A$1:$DT$1,0))),"]"),"$",2),"$"),":"&TEXTAFTER(TEXTBEFORE(TEXTAFTER(CELL("address",INDEX('IMPORT BASE'!$A$1:$DT$1,MATCH(AY1,'IMPORT BASE'!$A$1:$DT$1,0))),"]"),"$",2),"$")))

错误原因 #

1. 公式拼接的是文本字符串（如'IMPORT BASE'!CL:CL），但XLOOKUP的第三参数需要合法的单元格引用，直接传入文本会导致Excel无法识别。
2. 列标提取逻辑冗余，额外添加了不必要的单引号，进一步破坏了引用格式。

可行解决方案 #

方案1：使用INDEX直接返回目标列（推荐） #

跳过文本拼接步骤，直接用INDEX函数根据列号返回整列区域，避免格式错误：

=XLOOKUP(C2,'IMPORT BASE'!A:A,INDEX('IMPORT BASE'!$A:$DT,,MATCH(AY1,'IMPORT BASE'!$A$1:$DT$1,0)))

• 逻辑说明：MATCH(AY1,'IMPORT BASE'!$A$1:$DT$1,0) 获取目标列标题在首行的列号；INDEX('IMPORT BASE'!$A:$DT,,列号) 返回该列的整列数据，作为XLOOKUP的返回区域。

方案2：用INDIRECT转换文本引用 #

若需保留文本拼接逻辑，需用INDIRECT将拼接的文本转为合法引用，同时简化列标提取：

=XLOOKUP(C2,'IMPORT BASE'!A:A,INDIRECT("'IMPORT BASE'!"&SUBSTITUTE(CELL("address",INDEX('IMPORT BASE'!$A$1:$DT$1,MATCH(AY1,'IMPORT BASE'!$A$1:$DT$1,0))),CELL("row",INDEX('IMPORT BASE'!$A$1:$DT$1,MATCH(AY1,'IMPORT BASE'!$A$1:$DT$1,0))),"")))

• 逻辑说明：通过CELL函数获取列标题单元格地址，替换掉行号部分得到列标（如CL:），再拼接成完整区域文本，最后用INDIRECT转换为单元格引用。

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