You could do this with programming and an advanced filter or AutoFilter.
There's a sample there that uses and Advanced Filter to create a list
when a criterion is typed in a cell:

http://www.contextures.com/excelfiles.html

Under Filters, look for FL0002 - Product List by Week Number

James wrote:
Hi,

I have more than 1000 rows of data and the very first column has more than
100 values (i.e. Pencil shows in Cell A2, A18, A95, A357 and a number, 100,
shows in Cell A5, A58, A754, A951 and the rest of values has similar entries
in different cells in the first column.

I am trying to get the all the rows of data with Pencil in the first column
when I type Pencil in a cell.
Vlookup doesn't work since it only takes the very first cell that has Pencil.
Filter doesn't work well since there are two many values to choose from.

How do I set up a formula that retrieves a multiples rows of data with the
same one criteria in the first column?

Thanks,



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html