Excel: Searching for multiple terms in a cell and

Question

I am trying to scan a column and search for terms inside a cell. Once any of these terms are found, place the found one in another cell.

The terms are in a named range, I call it "namedrangeOfApps" with over 400 rows.

This is what I have now but instead of returning TRUE I want to return the actual value found.

 =SUMPRODUCT(--ISNUMBER(SEARCH({"namedrangeOfApps"},G2)))>0 

Example of a cell could be:

"Microsoft.Office.v.21" In this case, if either Microsoft and or Office is found" Place the found entry in another Cell instead of just placing TRUE.

Source of original solution

Answer 1

try this

=IFERROR(INDEX({NamedRange1},MATCH(TRUE,ISNUMBER(SEARCH({NamedRange1},A10)),0)),"")

or

=IFERROR(INDEX({"microsoft", "google", "apple"},MATCH(TRUE,ISNUMBER(SEARCH({"microsoft", "google", "apple"},A10)),0)),"")

This works great

reference...

Searching for multiple text strings in a MS Excel cell and return the same string when found

Answer 2

With text in cell A1 try:

=IF(ISNUMBER(SEARCH("Office ",A1)),"Office","") & IF(ISNUMBER(SEARCH("Adobe ",A1)),"Adobe","") & IF(ISNUMBER(SEARCH("google ",A1)),"google","") & IF(ISNUMBER(SEARCH("Microsoft ",A1)),"Microsoft","")

While it does appear a little "brute force", it is easy to understand and will return more than one keyword if more than one keyword is present.

EDIT#1:

Here is a small User Defined Function. Its arguments are the cell being searched and a list of keywords (in the sample, it is a Named Range called "mikee" in column C):

Option Explicit
Public Function RetrieveKeys(rin As Range, KeyList As Range) As String
    Dim s As String, r As Range

    s = rin(1).Text
    RetrieveKeys = ""
    For Each r In KeyList
        If InStr(1, s, r.Value) > 0 Then RetrieveKeys = RetrieveKeys & r.Value
    Next r
End Function

It can handle a very large list of keywords and if a separator is required between multiple returns, it is easy to change.