Google Sheets：二维表格中按范围查询取值的技术问询

核心需求 #

给定年份（如4）和具体数值（如1700），从二维表格中匹配对应年份行与数值所属区间列的交叉单元格值（如1.7），涉及的数值区间为：0-1000、1000-1500、1500-1800、1800-2300、2300-3000、3000-10000。

实现公式（两种方案） #

假设表格结构：

• 年份列：A2:A6（对应年份1-6）
• 区间表头：B1:G1（即上述6个区间）
• 数据区域：B2:G6
• 查询条件：年份存于J1，数值存于J2

方案1：INDEX+MATCH+数组公式（适配区间表头文本格式）

=INDEX(B2:G6, MATCH(J1, A2:A6, 0), MATCH(TRUE, ARRAYFORMULA(J2 >= LEFT(B1:G1, FIND("-", B1:G1)-1)*1) * (J2 < RIGHT(B1:G1, LEN(B1:G1)-FIND("-", B1:G1))*1), 0))

• 逻辑说明：
  1. MATCH(J1, A2:A6, 0)：精准定位目标年份所在的行
  2. ARRAYFORMULA(...)：批量判断数值J2是否落在每个区间内，返回布尔值数组
  3. MATCH(TRUE, ..., 0)：找到数值所属区间对应的列位置
  4. INDEX(...)：提取行与列交叉处的目标值

方案2：XLOOKUP简化版（手动指定区间下限）

如果不想依赖表头文本解析，可直接指定区间下限，公式更简洁：

=INDEX(B2:G6, MATCH(J1, A2:A6, 0), XLOOKUP(J2, {0,1000,1500,1800,2300,3000}, {1,2,3,4,5,6},,1))

• 逻辑说明：
  1. {0,1000,1500,1800,2300,3000}是各区间的下限值
  2. {1,2,3,4,5,6}对应区间的列序号
  3. XLOOKUP(...,1)：近似匹配，找到小于等于J2的最大下限，定位对应列

注意事项 #

• 若区间表头格式不是数字-数字，需调整LEFT/RIGHT的文本提取逻辑
• 年份列需无重复值，确保MATCH能精准定位
• 若需包含区间上限（如数值=1000时归到1000-1500区间），把公式中的<改为<=即可

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