create dynamic drop down list for large list of data

I hope so one can help me out. I know how to create a drop down list, and I know how to do it so that as a person types the list will search and find what they are looking for..

But my problem is it seems it doesn't work with a large group of Data, when to me that is when it is most useful.
I have a list of over 40,000 parts. However when i create the dropdown list..it will not do the search..I have to type the full name of the part in..That kinda defeat the purpose.. Anyone have any ideas.
here is how i been creating the list.

I would sort the list in alphabetical order, then highlight all the table and give it a name in the name box.
then i would hide all the rows of list of data.
Then just below the list i would select the cell go to validation = allow list, source = name of list.
this create the drop down list, and also allow you to do the typing and search feature as well.

Any suggestion of a better way of doing it? that does not include VBA or using any control boxes.
Oh and the list has more list where as they search and choose the part, it also populates the cost and price.


Thanks for any help you can offer