title: Excel check if cell contains text from list and return value from list
body: <p>I've seen many posts about the subject, but in general the question has been to return the value of a cell if the cell contains text from a list. I have used the following formula for that: </p>
  
  <pre><code>=IF(OR(INDEX(COUNTIF(Cell;"*"&amp;Array&amp;"*");));1;0)
  </code></pre>
  
  <p>However, what I want is to check if a cell contains text from a list, and if this is TRUE then show the text from the list, not the cell. A small example below.</p>
  
  <p>In column A I have the following values</p>
  
  <pre><code>abc123
  def123
  ghi123
  jkl123
  </code></pre>
  
  <p>In column B (the list)</p>
  
  <pre><code>abc
  def
  jkl
  </code></pre>
  
  <p>Now the first cell (abc123) is in the list, i want to show the text from the list (abc) in the formula cell.</p>
  
  <p>I hope this is clear.</p>