Google Sheets技术问询：基于指定名称实现数据查询及公式适配

基础背景 #

• 需基于指定名称从Sheet的名称列表中获取对应查询结果
• 现有下拉列表选项：
  • ALL PENDING LICENSE APPLICATION
  • ALL ISSUED LICENSE APPLICATIONM
  • ALL REJECTED LICENSE APPLICATION
  • ALL SUSPENDED LICENSE
  • ALL ISSUED NEW LICENSE NO

可用公式代码 #

结合现有逻辑与下拉选项，优化后的查询公式如下：

=QUERY(ALL!B3:K,
  "where D like '" & 
  XLOOKUP(A2, Conditions!A1:A, Conditions!B1:B) & "%' AND " &
  IF(A3="FEE NOT PAID", "K like '" & XLOOKUP(A3, Conditions!D1:D, Conditions!E1:E) & "%'", "H like '" & XLOOKUP(A3, Conditions!D1:D, Conditions!E1:E) & "%'"),
0)

如果需要为每个下拉选项单独定制更精准的匹配规则（比如不同选项对应不同列），可使用SWITCH函数扩展：

=QUERY(ALL!B3:K,
  "where " & 
  SWITCH(A2,
    "ALL PENDING LICENSE APPLICATION", "D like '" & XLOOKUP(A2, Conditions!A1:A, Conditions!B1:B) & "%'",
    "ALL ISSUED LICENSE APPLICATIONM", "D like '" & XLOOKUP(A2, Conditions!A1:A, Conditions!B1:B) & "%'",
    "ALL REJECTED LICENSE APPLICATION", "D like '" & XLOOKUP(A2, Conditions!A1:A, Conditions!B1:B) & "%'",
    "ALL SUSPENDED LICENSE", "D like '" & XLOOKUP(A2, Conditions!A1:A, Conditions!B1:B) & "%'",
    "ALL ISSUED NEW LICENSE NO", "D like '" & XLOOKUP(A2, Conditions!A1:A, Conditions!B1:B) & "%'"
  ) & 
  " AND " &
  IF(A3="FEE NOT PAID", "K like '" & XLOOKUP(A3, Conditions!D1:D, Conditions!E1:E) & "%'", "H like '" & XLOOKUP(A3, Conditions!D1:D, Conditions!E1:E) & "%'"),
0)

关键说明 #

• 保留原逻辑中XLOOKUP从Conditions工作表获取匹配关键词的逻辑，确保与预设映射规则一致
• 使用%通配符实现"以指定关键词开头"的模糊匹配
• 通过IF分支处理A3的状态判断，区分FEE NOT PAID时匹配K列、其他情况匹配H列的规则
• 注意下拉选项中的拼写误差（如ALL ISSUED LICENSE APPLICATIONM末尾多余的"M"），建议修正后使用，避免XLOOKUP匹配失败

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