Return all matching rows of a table based on dropdown cell

**excelling@excel** · 12-02-2012, 07:35 PM

I have a inventory list as follows:

Worksheet 1

Inventory ID	Product	Cost Price	Sale Price	Quantity Available	Date Ordered
20122	A	200	400	10	2012/01/19
20129	B	150	300	10	2012/01/20
20001	A	130	370	25	2011/09/14
20111	A	100	350	5	2011/09/01
22991	C	5	50	100	2012/11/25
22992	C	7	70	50	2012/11/29
29321	B	140	300	10	2011/09/29

I would like to create a worksheet 2 that has a dropdown that lists unique names from B2:B8 and acts as the criteria for lookup.
If I were to select "B" in the dropdown, worksheet 2 should return as many rows in the table that were matched from the dropdown criteria. For "B" it would match 2 records and it would show like:

Worksheet 2

Select Product:	B
Inventory	Cost Price	Sale Price	Quantity Available	Date Ordered
20129	150	300	10	2012/01/20
29321	140	300	10	2011/09/29

Another example is if I were to select "A" in the dropdown, then it should return all 3 rows that were matched:

Worksheet 2

Select Product:	A
Inventory	Cost Price	Sale Price	Quantity Available	Date Ordered
20122	200	400	10	2012/01/19
20001	130	370	25	2011/09/14
20111	100	350	5	2011/09/01

I've looked up various vlookup formulas searching around but they seem to apply to different cases and I didn't know how to adapt them to my worksheet. For example I tried array formula =INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) but it matches for two criteria. I also tried =IF(COLUMNS($C2:C2)<=$B2,
INDEX(Sheet1!$B$2:$B$9,SMALL(IF(Sheet1!$A$2:$A$9=$A2,
ROW(Sheet1!$B$2:$B$9)-ROW(Sheet1!$B$2)+1),
COLUMNS($C2:C2))),"")

but it only matched 1 column. The search results for this problem seem so pervasive but so different across cases. I don't really want to use filters or pivottables on the original worksheet 1, but instead would like to have the search results show in a seperate worksheet 2. I hope this is something that you can help me out with, it would really benefit me! I attached my sample worksheet now. Thank you for any help! Appreciate it