Elegant Solution for Searching for Multiple Strings in Multiple Cells using NAME?

[jbrousseau]

Hi everyone -

I currently have a working Excel Workbook that I am using to search multiple cells for an array of strings. I have condensed the strings in the array for purposes of posting on this site (there are dozens of strings to search for). Ideally, I'd like to put all the text in the array (ie., "Windows", "Win", etc.) into a NAME and just update the NAME as needed, but this has proven difficult.

In Cells A2:A1000, are values identifying the platform (eg., "Windows", "Linux", "Common").
In Cells B2:B1000, C1:C1000 and D1:D1000 are various strings of text.
In Cells E2:E1000, the formula: =IF(OR(A2="LINUX",A2="COMMON"),"N/A",IF(OR(COUNT(SEARCH({"Windows","Win","XP"},B2)),COUNT(SEARCH({"Windows","Win","XP"},C2)),COUNT(SEARCH({"Windows","Win","XP"},D2))), "VIOLATION", "COMPLIANT"))


Any help would be much appreciated.

Thanks!

 

[barry houdini]

You could list all the text somewhere on the worksheet then name that range TextList and use this "array formula"

=IF(OR(A2={"LINUX","COMMON"}),"N/A",IF(COUNT(SEARCH(TextList,B2&"-"&C2&"-"&D2)),"VIOLATION","COMPLIANT"))

formula needs to be confirmed with CTRL+SHIFT+ENTER

 

[jbrousseau]

Exactly what I needed.

Thank you so much, barry!

 

[jbrousseau]

Would it be possible to make this case-sensitive? I've been playing around with FIND, but I can't seem to make it work correctly.

 

[barry houdini]

Would it be possible to make this case-sensitive?

Which part? If you change SEARCH to FIND then it will be case-sensitive for the second part.