Searchable drop down list only searching for text at start of cells not anywhere in cell

[JoeBourneLancs]

Hi,

Like others on this forum I've used this video https://www.youtube.com/watch?v=Z-h2UER3b_0 to help build a form which contains a cell which searches a large prexisting data set as the user is typing to ensure that we have consistency across the data input where variable formats exist. For example, ensuring all users type "university of X" rather than some typing that and others typing "x university". I have followed the video and it all works very well... EXCEPT... when I add data validation (list) to the cell in which users will type into to generate suggested options I get a problem: The search no longer searches the entire string within the original data it only searches the start of the string.  Has anyone got a solution to this?

Here is the function I'm using as per the attached video: =FILTER(N2:N7008,ISNUMBER(SEARCH('Partnership Record'!D3,N2:N7008)),"not found")

Thanks,

Joe

[#2]

@JoeBourneLancs 

Better if you provide sample file to illustrate the issue.

[#3]

Hi,

I've created with formula the searchable drop-down lists on an invoice template. With this searchable drop-down lists, data is retrieved from the other sheet and product information is added to the invoice.

To review the searchable drop-down list construction steps and to download the sample file visit : Searchable drop down lists in Excel