Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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   |
share|improve this question

1 Answer 1

up vote 0 down vote accepted

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: enter image description here
      It will return TRUE if a person already has their "Dream Job".
    • Column E, needs to have this formula:
      enter image description here
      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:
    enter image description here
    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

share|improve this answer
1  
Thank you very much, shame I can't vote up yet! –  Jumbalaya Wanton Dec 9 '13 at 20:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.