Drop Down Box, return multiple Rows with multiple Columns - No VBA

I really feel like I'm missing something obvious, but I'm stuck ...

The basic idea is I'm wanting to produce the functionality of the AutoFilter - you select one of several choices of products and in the area below, all rows from the "masterlist" that match that product number are listed along with the associated 8 cells of data.

I don't want to use any VBA because the network security here is so tight I can't edit or implement most of those types of things.

I know the easy solutions is "just use autofilter", but the purpose of this spreadsheet is to have only a simplified interface for the end user.

I've been experimenting with VLOOKUP - I've used it before. And either it's not what I need to do, or if it is - I'm screwing something up.

What's the most efficient way to go about doing this?

Yes, that's what I'm calling the "auto-filter", sorry if that's not the correct name.

I don't want the entire list of data visible. I want the list hidden away from sight with only 1 available drop down box.

here's a small "sample". i can't really include actual data due to privacy agreements I'm bound to ... but you should get the picture. these are the actual columns of data. there are multiple products being tested and each of them may have different tests being performed - ranging from 3 to 12 possible analyticals

when you turn the filter on, click drop down and select say "purple ooze", it will show all the rows that have purple ooze as the product.

what i'm hoping for is to set up a page that would when selecting the appropriate product from the drop down, the area underneath would appear something like ...


product name
material #
Common name

test1 target value max min
test2 target value max min
test3 target value max min
...
up to however many tests are there.

I would then set up my print area so that the end user will select the product, and click print and see a quick snapshot of the tests that will need to be performed followed by the criteria.


i realize the ease of using the filter, but i need to (believe it or not) simplify it further, plus printing something that looks like a spreadsheet would complicate it further



thanks for any advice

That's EXACTLY what I was looking for ... INDEX and MATCH, funny I've never come across them before, but it seems like I really should have!

Thanks a bunch!