如何在Google Sheets的QUERY函数中嵌入IF逻辑按日期取对应列值

实现方式1：QUERY内嵌判断逻辑（性能最优） #

直接在QUERY的SELECT子句中写入两列日期的比较规则，不需要额外嵌套外层函数：

=QUERY(Database!C12:AM,"SELECT IF(AH > P, AH, P) WHERE AC CONTAINS 'PRICE | Time-limited save' AND AG = date '"&TEXT(Database!N2,"yyyy-MM-dd")&"' AND LOWER(Y) CONTAINS 'approved'")

如果出现列识别报错（查询范围非A列开头时容易触发），改用ColN格式的列索引即可，对应公式如下：

=QUERY(Database!C12:AM,"SELECT IF(Col32 > Col14, Col32, Col14) WHERE Col27 CONTAINS 'PRICE | Time-limited save' AND Col31 = date '"&TEXT(Database!N2,"yyyy-MM-dd")&"' AND LOWER(Col23) CONTAINS 'approved'")

列索引对应规则：查询范围从全表C列（第3列）开始，因此全表列号减2即为QUERY内的Col编号，对应关系如下：

• 全表P列 = query内Col14
• 全表AH列 = query内Col32
• 全表AC列 = query内Col27
• 全表AG列 = query内Col31
• 全表Y列 = query内Col23

实现方式2：先筛选后判断（逻辑更直观） #

先筛选出符合条件的两列日期，再做比较取值，不容易出现列对应错误：

=ARRAYFORMULA(LET(
  筛选结果, QUERY(Database!C12:AM,"SELECT P, AH WHERE AC CONTAINS 'PRICE | Time-limited save' AND AG = date '"&TEXT(Database!N2,"yyyy-MM-dd")&"' AND LOWER(Y) CONTAINS 'approved'"),
  IF(INDEX(筛选结果,,2) > INDEX(筛选结果,,1), INDEX(筛选结果,,2), INDEX(筛选结果,,1))
))

如果你的表格不支持LET函数，改用下方重复调用QUERY的兼容写法：

=ARRAYFORMULA(IF(
  QUERY(Database!C12:AM,"SELECT AH WHERE AC CONTAINS 'PRICE | Time-limited save' AND AG = date '"&TEXT(Database!N2,"yyyy-MM-dd")&"' AND LOWER(Y) CONTAINS 'approved'") > QUERY(Database!C12:AM,"SELECT P WHERE AC CONTAINS 'PRICE | Time-limited save' AND AG = date '"&TEXT(Database!N2,"yyyy-MM-dd")&"' AND LOWER(Y) CONTAINS 'approved'"),
  QUERY(Database!C12:AM,"SELECT AH WHERE AC CONTAINS 'PRICE | Time-limited save' AND AG = date '"&TEXT(Database!N2,"yyyy-MM-dd")&"' AND LOWER(Y) CONTAINS 'approved'"),
  QUERY(Database!C12:AM,"SELECT P WHERE AC CONTAINS 'PRICE | Time-limited save' AND AG = date '"&TEXT(Database!N2,"yyyy-MM-dd")&"' AND LOWER(Y) CONTAINS 'approved'")
))

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