google-sheets - 如果文本包含（过滤或查询），则按具有 2 个字段的数组过滤

Question

我需要帮助，因为我不知道如何制作这个公式。我尝试了 FILTER 和 QUERY 函数的多种变体，但仍然没有成功。

我有 2 张纸：

1. 用户 - 包含用户电子邮件和 2 个位置列：城市和州

2. LOCATIONS - 包含 2 列：城市和州 - 它是位置列表

我需要第三张表来列出位置在 LOCATIONS 表中列出的所有用户。每个用户都应该在自己的行中。

条件：

1. 提取的用户必须将城市和州列与 LOCATIONS 表中的列相匹配，以避免在我只需要其中一个时从多个位置（如俄勒冈州波特兰和德克萨斯州波特兰）获取用户

2. 如果用户在多个位置，则 USERS 中的城市列可能在单个单元格内有多个城市，用“，”分隔，因此需要通过“如果文本包含”条件过滤城市

这是示例表的副本：https ://docs.google.com/spreadsheets/d/1XruYIMq0nklFInqcGtzN7nd26rXTNnudsZNMI70uG4I/copy

标签： google-sheetsgoogle-sheets-formula

Answer 1

尝试：

=ARRAYFORMULA(IFNA(VLOOKUP(E2:E&"♥"&F2:F; 
 REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(TRIM(SPLIT(
 FLATTEN(IF(IFERROR(SPLIT(USERS!B2:B; ","))="";; 
 SPLIT(USERS!B2:B; ",")&"♥"&USERS!C2:C&"♠♦"&USERS!A2:A&",♦"&USERS!A2:A)); "♦")); 
 "select Col1,max(Col2) where Col2 is not null group by Col1 pivot Col3"); 
 "offset 1"; 0));;9^9)); "♠")); ",$"; ); 2; 0)))

---

更新：

=ARRAYFORMULA(QUERY({USERS!A:C, TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(IFERROR(SPLIT(USERS!B:B, ","))="",, 
 SPLIT(USERS!B:B, ",")&"♥"&USERS!C:C)),,9^9)))}, 
 "select Col1,Col2,Col3 where Col4 matches '.*"&TEXTJOIN(".*|.*", 1,
 IF(LOCATIONS!A2:A="",,LOCATIONS!A2:A&"♥"&LOCATIONS!B2:B))&".*'", 1))