search any of some words in a cell

[Stauricus]

hello everybody!
this problem is almost driving me crazy! it took all my day and i couldn't solve it!

i have a column made of 2160 cells. in each of these cells, there are many animals diseases names, i.e.: "Erlichiosis, Pyometra, and Leishmaniosis". and i have a list of diseases to search for in this column: "Erlichiosis", "Babesiosis", "Haemobartonelosis", and others.
all i want is a formula that return true if ANY of the words from the list is found in the current cell, and false otherwise.

i tried many functions, like IF, OR, AND, NOT, FIND, SEARCH, but to no avail...

any suggestion is welcome. thanks in advance!

[alansidman]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[kvsrinivasamurthy]

If A column contains list and search word is "Erlichiosis", below formula Gives TRUE , if it contains word else FALSE.

=COUNTIF(A1:A2160,"Erlichiosis")>0

[RichTea88]

Have you considered using a Macro for this? I'm sure there are ways to display all the results in another sheet.
This macro searches a number fo columns but doesn't find non exact criteria, but I'm sure some gentry on here would tweak it for you!

Please Login or Register  to view this content.