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:
USERS - contains user email, and 2 location columns: city and state
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:
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
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