if-statement - 来自多个动态下拉列表的 Google 表格匹配/查询

Question

我有一张工作表，我需要从另一个选项卡查询数据以匹配 4 个 if/或动态下拉列表和 1 个 if/and 动态下拉列表。这是我的一个障碍。我附上了表格以及 4 分钟的解释视频。提前致谢！

床单：

https://docs.google.com/spreadsheets/d/1rUtTJkvFKFI8Q6lnRtxNpky5bYe9u8BZBok0M8hwZ7o/edit?usp=sharing

视频：

https://www.loom.com/share/392fd41c5c0844d6ad7df308d1af9f6c

标签： if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Answer 1

尝试：

=ARRAY_CONSTRAIN(QUERY({'Sub List'!A2:AE, 
 TRANSPOSE(QUERY(TRANSPOSE('Sub List'!L2:U),,999^99)), 
 TRANSPOSE(QUERY(TRANSPOSE('Sub List'!V2:AE),,999^99))},
 "where "&TEXTJOIN(" and ", 1, 
 IF(B1="",,"Col32 contains '"&B1&"'"), 
 IF(B2="",,"Col32 contains '"&B2&"'"), 
 IF(B3="",,"Col32 contains '"&B3&"'"), 
 IF(B4="",,"Col32 contains '"&B4&"'"), 
 IF(B6="",,"Col33 contains '"&B6&"'"))&""), 999^99, 31)

---

用于OR逻辑用途：

=ARRAY_CONSTRAIN(QUERY({'Sub List'!A2:AE, 
 TRANSPOSE(QUERY(TRANSPOSE('Sub List'!L2:U),,999^99)), 
 TRANSPOSE(QUERY(TRANSPOSE('Sub List'!V2:AE),,999^99))},
 "where "&TEXTJOIN(" or ", 1, 
 IF(B1="",,"Col32 contains '"&B1&"'"), 
 IF(B2="",,"Col32 contains '"&B2&"'"), 
 IF(B3="",,"Col32 contains '"&B3&"'"), 
 IF(B4="",,"Col32 contains '"&B4&"'"), 
 IF(B6="",,"Col33 contains '"&B6&"'"))&""), 999^99, 31)

---

对于组合（OR黄色单元格和AND绿色单元格之间）

=ARRAY_CONSTRAIN(QUERY({'Sub List'!A2:AE, 
 TRANSPOSE(QUERY(TRANSPOSE('Sub List'!L2:U),,999^99)), 
 TRANSPOSE(QUERY(TRANSPOSE('Sub List'!V2:AE),,999^99))},
 "where ("&TEXTJOIN(" or ", 1, 
 IF(B1="",,"Col32 contains '"&B1&"'"), 
 IF(B2="",,"Col32 contains '"&B2&"'"), 
 IF(B3="",,"Col32 contains '"&B3&"'"), 
 IF(B4="",,"Col32 contains '"&B4&"'"))&")"&
 IF(B6="",," and Col33 contains '"&B6&"'"), 0), 999^99, 31)