autocomplete with vlookup providing multiple results

[ejcnj]

Hello and thank you in advance for any assistance provided.

Some background on what I am trying to accomplish.
Column A contains customer names
Column B contains customer account numbers.

I have created a auto complete sequence that will allow our call center people to type in a partial name, or part of a name and get a result. Then a second Vlookup provides the account number associated with the customer name that comes up in the auto complete which provides pricing based on that acct.. etc. etc.

The formula I am using is: =IF($A2="","",VLOOKUP("**"&$A$2&"*",$A$7:$B$700,1,FALSE)) (A2 being the "search box")

This works great, but some of our customers have similar names or key words in their name appear dozens of times. Like if they were to type in soccer, we have almost 2 dozen account names that contain soccer and that vlookup only provides the first account it comes across that contains the word soccer.

What I have been trying to do is figure out a way to use that auto complete Vlookup statement but have it spit out multiple results, capture those results in a list and create a drop down menu showing all the possible results based on what they typed in.

If there some kind of IF THEN statement that I can use telling subsequent vlookups to move on to the next closest match after the first has been identified?

I tried using an Index match... but I could not figure out how to incorporate the auto complete vlookup I have into it.

Any assistance would be greatly appreciated.

Thank you
Ed

[M E Good @ XL4M]

A1 Name, B1 Acct, C1 data1, D1 data2, E1 data3 headers, data in row 2
F2 =IF(ISNUMBER(FIND($J$1,A2)),ROW(),"") copy down to match col A
J1 enter search info
K2 =IF(ROWS($1:1)>COUNT($F:$F),"",INDEX(A:A,MATCH(SMALL($F:$F,ROWS($1:1)),$F:$F,0))) copy across to col O, select K2:O2 and copy down say 20 rows (enough to show all possible matches add more)
Create you dropdown menu from this shortlist.

[ejcnj]

Thank you for the reply M E GOOD.

I could not get it to work... and I posted an attachment for you to check out. But then I realized I am an Idiot and it was not working because I have to type in all CAPS to match the data source.
So Thank you so much for your assistance!! I very much appreciate it.