Excel function to search a string for a multiple keywords

Question

I have two tables. One of them has server names. The other has timestamps (first table, column A below) and text strings (first table, column B below). I want to search those strings for a keywords specified in the server table (second table below). If the match is found function writes to the cell name from the header of the column where the keyword is.

Example

I want to complete column System in Blue table. So for example C2 should show GreenSys and C8 - RedSys.

I have tried using SEARCH function but it looks like it tries to match whole table to the string if I pass it as an argument. VLOOKUP doesnt work too as I am using two tables. What's the best way for me to get this working?

Answer 1

Can you try this formula to cellC2?

=IF(SUMPRODUCT((B2=Sheet2!$A$2:$D$4)*COLUMN(Sheet2!$A$1:$D$1))>0,
INDEX(Sheet2!$A$1:$D$1,SUMPRODUCT((B2=Sheet2!$A$2:$D$4)*COLUMN(Sheet2!$A$1:$D$1)))
,"")

I have assumed that the second table is at Sheet2 and that data is upto column D, starting with the headers at A1, with the format you describe.

EDIT:

I can see you have amended the original post, and my answer no longer meets the specifications. Therefore I think it is best that I delete it.

EDIT2:

Added VBA solution. Assumptions:

• Orignal data table in Sheet1
• Destination table in Sheet2
• Headers of Sheet1 in 1st row

The below code was tested, it should be OK but needs error handling:

Sub moveData()
    Dim rngDestination As Range
    Dim lRowCounter As Long, lColCounter As Long, lValueCounter As Long, lLastRow As Long
    Dim vOriginArray As Variant, vValuesArray As Variant, vDestinationArray As Variant

    ' Database table in Sheet2
    vOriginArray = Sheet2.UsedRange.Value

    ' Destination table in Sheet1
    With Sheet1
        lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        ' Put the values we need to compare into an array
        vValuesArray = .Range(.Cells(2, 2), .Cells(lLastRow, 2)).Value
        Set rngDestination = .Range(.Cells(2, 3), .Cells(lLastRow, 3))
    End With

    ' We will store the values to an array first and then
    ' back to the sheet, it is faster this way
    ReDim vDestinationArray(1 To rngDestination.Rows.Count, 1 To 1)

    ' Loop through all rows and columns, exclude header row
    For lRowCounter = 2 To UBound(vOriginArray, 1)
        For lColCounter = LBound(vOriginArray, 2) To UBound(vOriginArray, 2)
    ' For each entry, find which values match and store them
            For lValueCounter = 1 To UBound(vValuesArray, 1)
                If InStr(1, vValuesArray(lValueCounter, 1), vOriginArray(lRowCounter, lColCounter), vbTextCompare) Then
                    vDestinationArray(lValueCounter, 1) = vOriginArray(1, lColCounter)
                End If
            Next lValueCounter
        Next lColCounter
    Next lRowCounter

    ' Put the data back to excel
    With rngDestination
        .ClearContents
        .Value = vDestinationArray
    End With
End Sub

Answer 2

If you change the way you have the data setup so that it is a bit more Excel-friendly, this can be rather easily accomplished.

The lookup sheet should look like this (the formula below has this as 'Sheet2'):

Then on your main data sheet, in cell C2 and copied down:

=IF(SUMPRODUCT(COUNTIF(B2,"*"&Sheet2!$A$2:$A$7&"*")),INDEX(Sheet2!B:B,SUMPRODUCT(COUNTIF(B2,"*"&Sheet2!$A$2:$A$7&"*")*ROW(Sheet2!$A$2:$A$7))),"")

The results look like this:

Answer 3

With the assumption that all Servers start with "Serv".. this should work without using vba.

=MID(B1,SEARCH("Serv",B1,1),IF(ISERROR(SEARCH(" ",B1,SEARCH("Serv",B1,1))),LEN(B1)-SEARCH("Serv",B1,1),SEARCH(" ",B1,SEARCH("Serv",B1,1))-SEARCH("Serv",B1,1)))

Essentially the formulas searches for the keyword serv and then attempts to parse until the end of the word to return the full name.

As someone else mentioned, it would be easier to do with vba but then again there is a benefit of not having macros.