0

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.

Community
  • 1
  • 1
Maximillian
  • 743
  • 2
  • 22
  • 41
  • 1
    Can you just use a wildcard in a vlookup? `=vlookup("*"&A2*"*", C:D, 2, false)` – JNevill Mar 10 '17 at 18:24
  • I will give it a shot, didn't think of that. – Maximillian Mar 10 '17 at 18:31
  • @JNevill I think I botched your sample in a test. I built a sample of what I'm trying to do. [What did I do wrong?](http://imgur.com/SfOs5Al) A2 should match on C4. – Maximillian Mar 10 '17 at 18:59
  • 1
    You've got a stray asterisk in there. It should look like `"*"&A2&"*"` 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` – JNevill Mar 10 '17 at 19:17
  • 1
    @JNevill That did the trick. Mind making an answer so I can give you credit? – Maximillian Mar 10 '17 at 19:18

1 Answers1

1

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.

JNevill
  • 46,980
  • 4
  • 38
  • 63