1

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

Improve this question

1 Answer 1

Reset to default
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)))

enter image description here

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, I should have mentioned that each user needs to be in its own row.
Example sheet should show only users whose location matches the ones in LOCATIONS sheet
Thanks for the answer! It's still missing one thing, which is the 1st condition in the original post, and that is why both users from Dallas TX and Dallas NC are showing up, and only Dallas TX is in the LOCATIONS sheet. Thanks again for your help!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.