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!
