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

Is something like this, you want?

Attached Files

DataValidationListFromSearch.xlsx (12.0 KB, 11 views)

Office 365?
See attached

Attached Files

msofficeforums46090.xlsx (10.8 KB, 11 views)

Thank you to both of you for responding.


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


Stuart