title: Google spreadsheets: if cell contains one of a list of predefined values
body: <p>I have a column in my spreadsheet that holds cells with strings of text like:</p>
  
  <ul>
  <li><em>Lorem ipsum blah blah category1</em></li>
  <li><em>Lorem ipsum blah blah category2</em></li>
  </ul>
  
  <p>I want to have a second column that will hold values dependent on whether the string contains <em>category1</em>, <em>category2</em>, etc. I can do this with something like:</p>
  
  <pre><code>=arrayformula(if(H32:H="","",(iferror(if(search("category1",H32:H),"The First Category"),iferror(if(search("category2",H32:H),"The Second Category"))))))
  </code></pre>
  
  <p>However, the list of categories itself is dynamic, so rather than hard code them into the formula, I would like to retrieve them from elsewhere in the spreadsheet, ie:</p>
  
  <pre><code>| Category1 | "The First Category"  |
  | Category2 | "The Second Category" | // I want to be able to add to this list and have the formula retrieve the values.
  </code></pre>
  
  <p>Does anyone have any idea how I could go about this?</p>
  
  <p>EDIT: Please see sample sheet <a href="https://docs.google.com/spreadsheets/d/1tfIIQ1aNOi8CbiORXr6o4i30f4de5wR3L2bsvSdObAg/edit?usp=sharing" rel="nofollow">here</a>. As you can see, the arrayforumula in A2 looks at the first 3 key values in E2 - E4, and gets the corresponding replacement values. However, I want to be able to add to the list of key/replacements without having to go and manually change the formula in A2 each time.</p>