title: Autofill based on list and value of a cell
body: <p>I'm making a spreadsheet to help me with my personal accounting. I'm trying to create a formula in LibreOffice Calc that will search in a given cell for a number of different text strings and if found return a text string.</p>
  
  <p><a href="https://i.stack.imgur.com/JzmHN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JzmHN.png" alt="Screen shot"></a></p>
  
  <p>For example, the formula should search for "burger" or "McDonalds" in <code>$C6</code> and likewise then return "Food" to <code>$E6</code>. It should not be case sensitive. And needs partially to match strings as well as in the case of Burger King. I need it to be able to search for other keywords and return those values as well, like "AutoZone" and return "Auto" and NewEgg and return "Electronics". </p>
  
  <p>I've had a tough time finding any kind of solution to this and the closet I could get was with a <code>MATCH</code> formula but once I nested it in an <code>IF</code> it would not work. I've also tried nested <code>IF</code> with <code>OR</code>; not joy on either. </p>
  
  <p><i>Examples:</i></p>
  
  <pre><code>=IF(OR(D10="*hulu*",D10="*netflix*",D10="*movie*",D10="*theature*",D10="*stadium*",D10="*google*music*")=1,"Entertainment",IF(OR(D10="*taco*",D10="*burger*",D10="*mcdonald*",D10="*dq*",D10="*tokyo*",D10="*wendy*",D10="*cafe*",D10="*wing*",D10="*tropical*",D10="*kfc*",D10="*olive*",D10="*caesar*",D10="*costa*vida*",D10="*Carl*",D10="*in*n*out*",D10="*golden*corral*",D10="*nija*",D10="*arby*",D10="*Domino*",D10="*Subway*",D10="*Iggy*",D10="*Pizza*Hut*",D10="*Rumbi*",D10="*Custard*",D10="*Jimmy*")=1,"Food",IF(OR(D10="*autozone*",D10="*Napa*",D10="*OREILLY*")=1,"AUTO","-")))
  </code></pre>
  
  <p>I can create a different table and make a lookup reference so another way to put this is I need something that does the opposite of what <code>VLOOKUP</code> and <code>HLOOKUP</code> do and return the header value for any data matching in given columns. </p>
  
  <p><em>Something like:</em> </p>
  
  <pre><code>=IF(NOT(ISNA(MATCH(A1,B3:B99))),B2,IF(NOT(ISNA(MATCH(A1,C3:C99))),c2,0))
  </code></pre>
  
  <p>If <code>A1</code> was the test and <code>B2</code> and <code>C2</code> were the headers and it was searching below those.</p>