Google Sheets多条件Vlookup需求：用ArrayFormula实现价格层级匹配

问题背景 #

• 现有两个工作表：
  • Database：存储产品完整信息，包含尺寸、成本、子类别等字段
  • PriceTiers：定义价格层级匹配规则，涵盖区间条件（如尺寸范围、成本范围）与精确匹配条件（如子类别编码）
• 需求：在Database表中实现批量自动匹配，用ArrayFormula一次性填充所有行的对应价格层级
• 尝试过的方法及问题：
  • 常规VLOOKUP拼接键值的方式无法处理>/<区间条件
  • 嵌套FILTER的ArrayFormula出现行匹配错位问题
  • QUERY函数因单元格引用重复无法适用
  • 单个DGET公式可实现单行匹配，但无法批量应用（手动复制5000行效率极低）

单行匹配的DGET公式 #

=IF(ROW(A1:A)=1,"Price Line Lookup",IFERROR(IF(A1="",,
IF(I1>50,
DGET(PriceTiers!A:AC,"Price Line Code",{"Subcategory Code","SU-Min","SU-Max","Cost_Min","Cost_Max";H1,"<="&X1,">="&X1,"<="&AT1,">="&AT1}),
DGET(PriceTiers!A:AC,"Price Line Code",{"Subcategory Code","Size_Min","Size_Max","SU-Min","SU-Max","Cost_Min","Cost_Max";H1,"<="&W1,">="&W1,"<="&X1,">="&X1,"<="&AT1,">="&AT1}))))

批量解决方案：结合BYROW的ArrayFormula写法 #

使用BYROW遍历每行数据，将单行DGET逻辑批量应用，无需逐行复制公式：

=ArrayFormula(
  IF(ROW(A:A)=1,"Price Line Lookup",
    IF(A:A="","",
      BYROW(A:AT,LAMBDA(row,
        IFERROR(
          IF(INDEX(row,9)>50,
            DGET(PriceTiers!A:AC,"Price Line Code",{"Subcategory Code","SU-Min","SU-Max","Cost_Min","Cost_Max";INDEX(row,8),"<="&INDEX(row,24),">="&INDEX(row,24),"<="&INDEX(row,46),">="&INDEX(row,46)}),
            DGET(PriceTiers!A:AC,"Price Line Code",{"Subcategory Code","Size_Min","Size_Max","SU-Min","SU-Max","Cost_Min","Cost_Max";INDEX(row,8),"<="&INDEX(row,23),">="&INDEX(row,23),"<="&INDEX(row,24),">="&INDEX(row,24),"<="&INDEX(row,46),">="&INDEX(row,46)})
          )
        )
      ))
    )
  )
)

公式说明 #

• BYROW(A:AT, LAMBDA(row, ...))：遍历Database表中A到AT列的每一行，将当前行数据存入row变量
• INDEX(row, n)：提取当前行对应列的数据（例如INDEX(row,9)对应原公式中的I列，INDEX(row,8)对应H列）
• 保留原DGET的匹配逻辑，根据I列数值判断使用哪一套匹配条件，自动从PriceTiers中匹配对应价格层级编码
• 外层IF和IFERROR处理表头、空行及匹配失败情况，避免出现错误值

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