Excel Drop Down List - typing first letter selects matching names in the Lookup range

[PhilbyUK]

I have created a simple Dropdown List in an input cell in spreadsheet 1 of a workbook that references a lookup range on spreadsheet 2 within the same workbook.  This is to ensure correct data validation of the cell entry in spreadsheet 1.  So far, standard dropdown list stuff

It's a long list so rather than having to scroll up/down to find the input entry from the list, clicking to select and moving on,  I want to start typing the first few letters of the LookUp range item in spreadsheet 1 input cell.  Once the correct value appears then ENTER or TAB to the next input cell, so that the correct input is always entered.

eg: Start typing "do" in the input cell in spreadsheet 1 and  "Dover, Doncaster..." appears as input options drawn from the lookup range in spreadsheet 2.  I can then click select the correct input option

How do I do this without complicated and buggy VBA?

[#2]

@PhilbyUK Google for "searchable dropdown excel" and you'll find plenty of resources that could help you achieve what you need.

[#3]

@PhilbyUK  as @Riny_van_Eekelen mentioned you can google search and many options will come up.  Here is one: https://www.xelplus.com/searchable-drop-down-list/  

A couple things to note. 

A) basically all you are doing is referring to a list that is basically a 'helper' list (a helper column that creates a filtered version of the original list) that is filtered based on the value in the cell you are entering.  So actual list in col A then in col B you have something like Filter(A:A, isnumber(search(C1, A:A))) and then the drop down in cell C1 is based on col B.  This example and the link are relying on the new FILTER() function.  Without that function it gets more complicated.  Also, using SEARCH will filter the list with matches anywhere in the word i.e. your 'do' example would also show "Andover" and "Zanador".  If you do NOT want that feature you can tweak the FILTER condition to use something like LEFT(A:A,LEN(C1))=C1 

B) because you must let the user enter a character or 2 you can't force the input to ONLY be in the list.  So although it helps and encourages the user to pick valid values from the list they could type something else

C) I have seen comments in threads that on a Mac it requires the user to hit ENTER/TAB/etc before the drop down list is updated so they type, hit enter, then go back and click on the drop down.

D) I have also seen a comment that there might be issues if you have the helper (filtered) list on a different sheet than the drop down so you might need or want to 'hide' it in a hidden column on that sheet.

hope that all helps.

[#4]

@PhilbyUK 

Look no further! Here's my version of Excel Searchable Lists: 

https://techcommunity.microsoft.com/t5/excel/excel-searchable-lists/m-p/737982/highlight/true#M33995