Drop down list conditioning on text with wildcards

[Stuart_Brennan]

Hi,




I have the following problem that I am trying to resolve. I have a data set consisting of multiple columns but for ease lets just use 2 columns A & B. Both columns contain text in each cell that is comprised of multiple words.


I want to be able to type a value into cell D1 that in effect is being used as a search term. In cell E1, I then want a dropdown list that contains all the values from column B where the adjacent cell in column A contains the search term.


I have tried index, match formula within the data validation list formula but the validation list only contains the first matching value


The formula I have within the data validation - list - source field is :


=INDEX('All stock'!$A:$B,MATCH("*"&D1&"*",'All stock'!B:B,0),2)


Can anyone help ??


Thank you


Stuart

[ArviLaanemets]

Is something like this, you want?

[p45cal]

Office 365?
See attached

[Stuart_Brennan]

Thank you to both of you for responding.


@ArviLaanemets - your solution worked brilliantly. Thank you so much.


Stuart