[SOLVED] Search a column to find if string is contained in each cell of another column, return third value

Search a column to find if string is contained in each cell of another column, return third value

This is similar to this question, which gets me halfway to what I want to solve.

I've got a spreadsheet laid out like this:

Column A has IPv4 addresses
Column B is where I want to write my results.
Column C is a set of strings that might contain IPv4 addresses within them.
Column D contains additional data about column C.

The pseduocode/logic is this:

For each cell in B, if the text of the adjacent A exists anywhere in the cells of Column C, return the text from D that is directly next to C where the match was found.

The question I linked above allows me to find exact matches and return a value, but it doesn't help me find the data in situations where the match I am looking for is inside additional text. For example, B2 contains:

=INDEX(D:D,MATCH(A2,C:C,0))

I suspect I need to include when referencing column C ISNUMBER(SEARCH(substring,text)) to the formula, but I have yet to make it work.

Solution

You can use a wildcard inside of a vlookup function to accomplish this:

=VLOOKUP("*"&A2&"*", C:D, 2, false)

That says: take an asterisk "*" and concatenate it & to A2 and then concatenate that & to another asterisk "*". This will be interpreted as *192.168.0.24* that it will search for in column C.