title: Excel VLOOKUP and SEARCH combination
body: <p>I'm trying to search for part of a text string, in a column of text and return the second column. Hopefully this will make more sense with an example (note that this example is made up - I cannot post the exact data I'm using but this is similar to it).</p>
  
  <p>For example:</p>
  
  <pre><code>A                            D               E
  
  Really good dog             Good dog         text1
  red dog collar              Brown dog        text2
  Brown Toy dog               big dog          text3
                              dog collar       text4
                              dog walking      text5
  
  
   A                    B       
  
  Really good dog       text1
  red dog collar        text4
  Brown Toy dog         Not Found
  </code></pre>
  
  <p>So column D is searched through to provide a match to the first value in column A, if there's a match it returns the corresponding text in column E, if there is no match it returns "Not Found". As you can see from the expected results, column B, the string "Good dog" appears within "Really good dog" so "Really good dog" has a result of "text1". Note that case does not matter.</p>
  
  <p>I've been playing around with </p>
  
  <pre><code>=(VLOOKUP("*"&amp;DCE!X2&amp;"*",Lookups!$K$2:$L$19,2,FALSE))
  </code></pre>
  
  <p>which does not work because it's not all of the text string in the cell that could be matched to a value.
  I've tried using</p>
  
  <pre><code>=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($A$1:$A$3,C1)))))&gt;0,"1","") 
  </code></pre>
  
  <p>which would be great if I could swap the range with the cell within SEARCH.</p>
  
  <p>I know there's a MATCH version of these formulae but I haven't been able to get that to work either.</p>
  
  <p>The only formula I have working is a huge nested IF statement </p>
  
  <pre><code>=IF(ISNUMBER(SEARCH(Lookups!$K$2,X2)),Lookups!$L$2,IF(ISNUMBER(SEARCH(Lookups!$K$3,X2)),Lookups!$L$3,IF(ISNUMBER(SEARCH(Lookups!$K$4,X2)),Lookups!$L$4,IF(ISNUMBER(SEAR......
  </code></pre>
  
  <p>which I do not want to be creating for 100+ items to search for. This sees if the first value in the search column (column D) is contained within the lookup string (column A) and if it is it returns the result (column E) and if it is not it returns "Not Found".</p>
  
  <p>Also, the cell references in the above formulae do not directly relate to the example, they're just examples of the formulae I have been trying (I've found on various posts on this site).</p>
  
  <p>Any advice would be greatly appreciated. Sorry if this is difficult to follow through, I'm fairly new to Stackoverflow.</p>