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

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

-
Thank you very much, shame I can't vote up yet! – Jumbalaya Wanton Dec 9 '13 at 20:58