Partial string lookup

[jprobst]

Sample File here or Observation .xlsx

What I need to do is search the text strings in the "Evidence" column, match any words within the "Keywords" sheet, and then return the column heading (from the Keywords sheet) to the "Domain" column in the "Evidence" sheet.

Any ideas? I feel like I do not know enough of the terminology to be able to search properly (been trying), so if you can point me in the right direction, I would appreciate it.

[FDibbins]

Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

[humdingaling]

see attached my attempt on this....

i had to change the Keywords section and fill all the blanks with "zzzzzz"
the reason for this was because it would return blanks as found keyword (which is correct)

CSE formula was used...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Please Login or Register  to view this content.

the formula looks for "evidence" in keywords!A2:h20 and if it returns a zero (which means no matches) it will make the cell "N/A" <<change this to whatever you want
if it is not 0 then it uses index formula to match against column

now...if more than one keyword is found
it will return the domain closer to the left regardless of the order of words in evidence

[jprobst]

Awesome that works.
What if I want to return ALL domains that apply? For instance: 2A, 3B

[FlameRetired]

What if I want to return ALL domains that apply? For instance: 2A, 3B

If you are open to a VBA solution this User Defined Function by tigeravatar does that. It is already installed in the VBA editor of the attached. To see it open the VBA editor (keyboard shortcut is Alt + F11). In order to do it yourself copy the below, click Insert in the VBA editor then Module. A code module appears. Paste the code into that window.

Please Login or Register  to view this content.

Then this array-entered formula (Ctrl + Shift + Enter) in C2 and filled down.

Please Login or Register  to view this content.

[humdingaling]

it would be extremely difficult to do it in with the method i proposed

UDF method flame has provide would be the optional method in my opinion