title: Search for a substring in cell and return value of matrix
body: <p>the following excel tables are given:</p>
  
  <p><strong>Sheet01</strong><br>
  String: A <br>
  Output: B</p>
  
  <pre><code>+---------------------+--------------+
  |       String        |    Output    |
  +---------------------+--------------+
  | ABC Test01          | It is Test01 |
  | DEF Test01          | It is Test01 |
  | Test01 GHI          | It is Test01 |
  | Hellow Test02 World | Wow Test02   |
  | Test02 Sum Sing     | Wow Test02   |
  +---------------------+--------------+
  </code></pre>
  
  <p><strong>Sheet02</strong>
  <br>
  Search Criteria: A
  <br> OutputThis: B</p>
  
  <pre><code>+-----------------+--------------+
  | Search Criteria |  OutputThis  |
  +-----------------+--------------+
  | Test01          | It is Test01 |
  | Test02          | Wow Test02   |
  +-----------------+--------------+
  </code></pre>
  
  <p><br>
  So basically I want to find out if <em>Search Criteria</em> in Sheet02 can be found in <em>String</em> in Sheet01. <br> If so, display in <em>Output</em> (Sheet01) value of <em>OutputThis</em> (Sheet02).</p>
  
  <p>Following works for exact match: <br></p>
  
  <pre><code>=INDEX(Sheet02!B:B,MATCH(A2,Sheet02!A:A,0))
  </code></pre>
  
  <p>Now I simply tried to put in the like operator which doesn't make any sense. Because excel can't now what part of String is to be found in Search Criteria.</p>
  
  <p>What I'm looking for is something <br></p>
  
  <pre><code>=New_Function(SearchCriteriaMatrix, SearchCell, OutputMatrix)
  </code></pre>
  
  <p><br>
  <strong>EDIT:</strong></p>
  
  <p>I just used the following code and it somehow works:</p>
  
  <p><code>=INDEX(Sheet02!B:B,Match(A2,Sheet02!A:A,-1))</code>
  <br>
  The key is "-1". It changes the criteria from an exact match to a broad match. At least that's what I want it to do, but it doesn't workout well. Perhaps someone can use this and help out.</p>