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?

Solution

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:

Comments

[andflow]

I changed the sheet names but the formula writes nothing. Not an error, the cell is just empty.

[Ioannis]

that is weird, I just tested it. If you test it with the amended data (ServG2 has started), it will fail..(check my edit)

[andflow]

I see it now. It also works fine if I only leave server names in there too! But that's not the case with the data I have however. B2 has a lot of text in it and I only want to search by one word. Is it possible to adjust your formula to accommodate that?

[Ioannis]

I am not sure this is possible.. I will check it later tonight, and leave this post here for a while (maybe someone figures it out sooner). By the way, this can be done very easily with VBA, in case you are willing to go down this route..

[andflow]

If it gets me the result I need, sure.

[Ioannis]

+1 For thinking out of the box :) but not sure how easy the rearrangement step is..

[Ioannis]

I added a VBA code that should do what you need - I hope it helps!