如何在非常规格式分块利率表中用INDEX数组模式跨区查找值？

一、基于INDEX引用模式的实现（兼容全Excel版本） #

你的思路是可行的，利用INDEX的引用模式（第一个参数传入多个区域的数组），结合MATCH定位行号，可实现跨子表查询。以下是具体操作步骤：

公式逻辑拆解 #

1. 定位目标子表：根据输入利率的整数百分比部分，确定对应子表的area_num。例如4.375%的整数部分是4，对应4%子表的序号（按3%→1、4%→2…18%→16的规则映射）。
2. 定位年龄行：在目标子表的年龄列中，用MATCH找到输入年龄的精确行号。
3. 提取对应值：通过INDEX从目标子表的数值列中提取对应行的值。

示例公式 #

假设：

• 输入年龄存于K2，输入利率存于L2
• 3%子表区域为$C$2:$D$100（C列=年龄，D列=数值），4%子表为$F$2:$G$100，…，18%子表为$XX$2:$YY$100（按实际表格位置调整区域）

=IFERROR(
    INDEX(
        ($C$2:$D$100,$F$2:$G$100,$H$2:$I$100,...,$XX$2:$YY$100),
        MATCH(K2, INDEX(($C$2:$C$100,$F$2:$F$100,...,$XX$2:$XX$100),0,0,INT(L2*100)-2),0),
        2,
        INT(L2*100)-2
    ),
    "无匹配数据"
)

公式细节说明

• INT(L2*100)-2：将利率转换为整数百分比后，减去2得到对应子表的area_num（3%对应3-2=1，4%对应4-2=2，以此类推）。
• 内层INDEX(...,0,0,area_num)：提取目标子表的年龄列，供MATCH查找行号。
• 外层INDEX的第3参数固定为2：因为每个子表的第2列是需要返回的数值列。
• IFERROR：处理利率超出3%-18%范围或年龄不存在的异常情况。

二、Excel 365/2021 优化方案（用LET提升可读性） #

如果使用支持LET函数的新版本Excel，可将变量封装，公式更易维护：

=LET(
    target_age, K2,
    target_rate, L2,
    rate_int, INT(target_rate*100),
    area_num, rate_int-2,
    age_range, INDEX(($C$2:$C$100,$F$2:$F$100,...,$XX$2:$XX$100),0,0,area_num),
    value_range, INDEX(($D$2:$D$100,$G$2:$G$100,...,$YY$2:$YY$100),0,0,area_num),
    IFERROR(XLOOKUP(target_age, age_range, value_range, "无匹配数据"), "无匹配数据")
)

三、方案对比与最优选择 #

• INDEX引用模式：兼容所有Excel版本，稳定性高，无volatile函数（避免公式反复重算），是跨子表查询的经典方案，适合大多数场景。
• XLOOKUP+LET方案：仅支持新版本Excel，可读性和可维护性更强，公式结构更清晰。

注意事项 #

• 确保所有子表的年龄列均为升序排列，否则MATCH/XLOOKUP的精确匹配会失效。
• 所有子表的行范围需一致（比如都是从第2行到第100行），避免行号定位错误。

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