title: Excel / LibreOffice Calc Partial Match in Reverse
body: <p>I would like to use a lookup table to choose a tag for each row according to its description column. Descriptions contain keywords that map to tags. Therefore, I need to partially match against the keyword list as below:</p>
  
  <pre><code>  A                     B          C        D        E
  1 Description           Tag                 Keyword  Tag
  2 lorem KEYA ipsum                          KEYA     Tag A
  3 dolor sit KEYC amet                       KEYB     Tag B
  4 KEYB consectetur                          KEYC     Tag C
  5 adipiscing elit KEYA                      KEYD     Tag D
  6 sed do KEYB eiusmod                       
  </code></pre>
  
  <p>I would like to fill cells in column B with values from the lookup table in D2:E5. I could use something like</p>
  
  <blockquote>
    <p>=VLOOKUP(".<em>"&amp;$A2&amp;".</em>", $D$3:$E$6, 2, 0)</p>
  </blockquote>
  
  <p>but it won't work because I try to match full text against partial texts.</p>
  
  <blockquote>
    <p>=VLOOKUP($A2, ".<em>"&amp;$D$3:$E$6&amp;".</em>", 2, 0)</p>
  </blockquote>
  
  <p>does not work either. Apparently regular expressions are only supported in the search criterion. And I don't like to write something like</p>
  
  <pre><code>=IF(ISNUMBER(SEARCH("KEYA",$A2)), "Tag A", 
   IF(ISNUMBER(SEARCH("KEYB",$A2)), "Tag B", 
   IF(ISNUMBER(SEARCH("KEYC",$A2)), "Tag C", 
   IF(ISNUMBER(SEARCH("KEYD",$A2)), "Tag D", 
   ""))))
  </code></pre>
  
  <p>Do you have any suggestions?</p>