google sheets - Filter by array with 2 fields if text contains (filter or query)

Question

I need help as I don't know how to make this formula. I tried multiple variations with FILTER and QUERY functions, but still no success.

I have 2 sheets:

1. USERS - contains user email, and 2 location columns: city and state

2. LOCATIONS - contains 2 columns: city and state - it's a list of locations

I need a third sheet that would list all users whose location is listed in LOCATIONS sheet. Each user should be in its own row.

Conditions:

1. Extracted users must match both city and state columns to those in LOCATIONS sheet, to avoid getting users from multiple locations like Portland, OR, and Portland, TX, when I need just one of them

2. City column in USERS might have multiple cities separated by ", " inside a single cell if the user is in multiple locations, so city needs to be filtered by "if text contains" condition

Here's a copy of an example sheet: https://docs.google.com/spreadsheets/d/1XruYIMq0nklFInqcGtzN7nd26rXTNnudsZNMI70uG4I/copy

Answer 1

try:

=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)))

---

update:

=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))