Google spreadsheet: Lookup values between two tables

Question

I have two tables, people and jobs, on two separate sheets. How do I find the names of the people who:

1. Are not in their dream jobs
2. Their 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   |

Answer 1

I used the following approach for that.

Approach

1. You 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.
2. 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.

Example

I've created an example file for you: Lookup values between two tables