title: Excel formula to apply a type of true/false condition
body: <p>I have two excel files; the original file contains 10K+ patient names and medical condition, the goal is to identify patients (about 400+) with special conditions so that the mail that gets sent to them is different than the rest of the list.</p>
  <p>Original File Template:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Last Name</th>
  <th>First Name</th>
  <th>Diagnosis</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Doe</td>
  <td>John</td>
  <td>Cancer</td>
  </tr>
  <tr>
  <td>Smith</td>
  <td>John</td>
  <td>HIV</td>
  </tr>
  <tr>
  <td>Smith</td>
  <td>Jayne</td>
  <td>Broken Arm</td>
  </tr>
  <tr>
  <td>Rock</td>
  <td>Dwayne</td>
  <td>Common Cold</td>
  </tr>
  <tr>
  <td>Foster</td>
  <td>Jane</td>
  <td>Common Cold</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Mailing Template:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Last Name</th>
  <th>First Name</th>
  <th>Type of Mail</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Doe</td>
  <td>John</td>
  <td></td>
  </tr>
  <tr>
  <td>Smith</td>
  <td>John</td>
  <td></td>
  </tr>
  <tr>
  <td>Smith</td>
  <td>Jayne</td>
  <td></td>
  </tr>
  <tr>
  <td>Rock</td>
  <td>Dwayne</td>
  <td></td>
  </tr>
  <tr>
  <td>Foster</td>
  <td>Jane</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>In the Mailing Template, I want to classify the Type of Mail based on the diagnosis. Common diagnosis would be &quot;LV1&quot; and anything that I would identify as a special diagnosis, like cancer or HIV, would be &quot;LV2&quot;</p>
  <p>My initial approach would be to filter the Original File by the special diagnosis and then use a True/False condition of that filtered list against the Mailing template and manually flag LV1 or LV2. But is there a method or formula that could scan the Original File to look for the keywords (eg cancer and HIV) and automatically assign the corresponding names in the Mailing List with &quot;LV1&quot; or &quot;LV2&quot;?</p>