title: Get one of specific words from a text string. Excel 2016+
body: <p>I have a list of words I need to get from a text string:</p>
  
  <ul>
  <li>A cow</li>
  <li>Dog-boy</li>
  <li>Hello-get it</li>
  </ul>
  
  <p>(just random words)</p>
  
  <p>I have multiple cells with random words surrounding desired ones:</p>
  
  <ul>
  <li>Random wordlsads <strong>A cow</strong> askjdhakj </li>
  <li>sla;alsj <strong>Dog-Boy</strong> l;sasla</li>
  <li>skla <strong>Hello-get it</strong> ksalksajsa</li>
  </ul>
  
  <p>And I need an excel function to extract my specified words from this cell. The problem is that I need 1 universal formula that checks all words in a string and returns if something matches one of the words in a list.</p>
  
  <p>I would use following function to extract one word from 1 cell:</p>
  
  <pre><code>=TRIM(MID(D4,SEARCH(R3,D4),LEN(R3)))
  </code></pre>
  
  <p>Where <strong>D4</strong> is a text string I need to check and <strong>R3</strong> is a word I am looking for.</p>
  
  <p>I was trying to combine those using IFS formula:</p>
  
  <pre><code>=TRIM(IFS($R$4=MID(D2,SEARCH($R$4,D2),LEN($R$4)),$R$4,$R$2=MID(D2,SEARCH($R$2,D2),LEN($R$2)),$R$2))
  </code></pre>
  
  <p>But, as some of you already get, it returns <strong>#VALUE!</strong> error, because when first SEARCH formula runs and does not find any match it returns <strong>#VALUE!</strong> error and whole function returns it, despite the fact that it could actually have 1 TRUE result.</p>
  
  <p>I googled a lot and, as I understand, I need to use TRANSPOSE formula to create an array, and somehow get result from there, but, I guess, I am not that smart yet.</p>
  
  <p>Would appreciate some help on this one. Thank you!</p>