CODEWITHSUNDEEP
excelif-statementexcel-formulaworksheet-functionexcel-2016
Maximillian
Maximillian

Reputation: 754

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:

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.

Upvotes: 1

Views: 7332

Answers (1)

JNevill
JNevill

Reputation: 50218

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.

Upvotes: 3

Related Questions