Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a spreadsheet made up of several individual sheets: one sheet for each category of product and one sheet designed to be a "global view." In each sheet is a 3-column range of variable length with the headings "Name", "Status", "Material type". These ranges are named so that I can refer to them easily. Something like this:

Rangename = "Portnoy"

       [D]         [E]              [F]
[1]    **Name**    **Status**        **Material Type**
[2]    Portnoy     Pending           Other 
[3]    Sinclair    Complete          Case
[4]    Jeffries    Pending           Other
[5]    Davis       Complete          Case

On another sheet, I would like to mirror only those cells under the headings **Name** and **Status** whose material type is case. I can sort of make this work using VLOOKUP, but I've only gotten so far as making it work for one individual cell; I can't make it so both the name and status appear in adjacent cells, and I definitely can't get it to spit out the entire range. Ideally results would look like this, generated with one function:

Sinclair    Pending
Davis       Complete

The goal is to have a global view whose statuses will update dynamically. I know that ARRAYFUNCTION has a role to play here, but I'm stymied. All suggestions are appreciated!

share|improve this question
Can you share a doc with us? – Jacob Jan Tuinstra Jul 24 '13 at 20:48
Why is Sinclair Pending? If you used say 0 for Pending and 1 for `Complete1 then a pivot table report might serve you. – pnuts Feb 16 '14 at 10:58
up vote 0 down vote accepted

This is exactly the job of the QUERY command:

=QUERY(Sheet1!D:F, "select D,E where F='Case'")


Sinclair    Complete
Davis       Complete
share|improve this answer

Your Answer


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.