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

Your Answer


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

Browse other questions tagged or ask your own question.