Serching for cells in a table.

Hi There.

I am trying to search for a Product by inputting part of the name only.
I have a table of data that contains products, units, values and codes in columns.
what I would like help on is how do I search this table for rows that contain the text I have inputted (in column one -Products), this should get multiple results.
obviously the more text that is inputted - the fewer results as there are less variables. until there are only a few results and I can click on the one I want and it will insert the row of data in the cells to the right of the cell used for input.
I can do it with lists of 'categories' followed by 'INDIRECT' lists of 'sub categories' followed by VLookup. But then I have to go through my table and create multiple categories and sub-categories.

Thanks Alan.
A little advanced for me at presant.

I have attached a sample file to explain myself better.
there is a "Table1" in columns N-O-P-Q.
What I would like to do is enter some text into A8 say '100' and get excel to search column N for every instance of text that contains '100' in it.
then have all these displayed maybe in a box so that if I click on the one I want to use (in the box) it will enter column N result in 'C8', Column O in 'B8, Column P in 'E8' and column Q in 'D8'

Like I said before I can get the later results by using 'INDIRECT' and 'VLOOKUP' but the tricky part is geting a result in 'C8' from an input in 'A8'
Any thoughts?

what version of excel are you using ?

i'll upload a solution anyway, but not sure if it will work for 97-2003

it's added a few more columns to display values for the rows that find text in A8

in your display area (B-E) it lists the non blank rows from the new columns

i'm now thinking you'll want a more complicated search method now though

in fact, i think i got it totally wrong, i now see that you want some kind of combo box to appear on each new line

Hi.

I am using 2007 at the moment.

Opp's
I've got 2003 not 2007.

Thanks Alan.
Right, I have updated my profile.

I guess what I am trying to achieve is a drop down box in 'A8' that searches Column N or Table1,1 every time text is inputed.
Should I be looking a creating a combo box which is new to me.