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!

  • Can you share a doc with us?
    – Jacob Jan
    Commented Jul 24, 2013 at 20:48

1 Answer 1


This is exactly the job of the QUERY command:

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


Sinclair    Complete
Davis       Complete

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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