I have two tables, people and jobs, on two separate sheets. How do I find the names of the people who:Are not in their dream jobsTheir dream job is available in the jobs table?People|     | A    | B       | C         | D             || 1   | name | Job     | Dream Job | In Dream Job? || 2   | Joe  | Dentist | Plumber   | False         || 3   | Jim  | Waiter  | Laundry   | False         || 4   | Jane | Author  | Author    | True          || 5   | Jack | Brewer  | Wretch    | False         |Jobs|      | A  | B       || 1    | id | Job     || 2    | 1  | Dentist || 3    | 2  | Waiter  || 4    | 3  | Author  || 5    | 4  | Plumber || 6    | 5  | Laundry |In this case, the expected output would be the following:|     | A    | B       | C         || 1   | name | Job     | Dream Job || 2   | Joe  | Dentist | Plumber   || 3   | Jim  | Waiter  | Laundry   |
Google spreadsheet: Lookup values between two tables
google spreadsheets
I used the following approach for that.ApproachYou need to alter the people's sheet in the following manner  Column D, needs to have this formula:It will return TRUE if a person already has their Dream Job.  Column E, needs to have this formula:It will look for matches in the Jobs sheet. If an error arises (nothing found), then it displays TRUE, else FALSE. Both ARRAYFORMULA functions will take on a range, and thus spare you the effort in copying down the formula's.  A result sheet can look like this:The QUERY formula is capable of selecting different columns, unlike the FILTER formula. The WHERE clauses are setting the query to show only those entries that don't have their dream jobs and the one they picked also was mentioned in the jobs sheet. Notice that the FALSE statements are truly boolean and not text.ExampleI've created an example file for you: Lookup values between two tables