title: Searching for multiple substrings in random order in Google Sheets
body: <p>I've got a problem that involves matching sets of unordered substrings: easiest to lay it out first I think:</p>
  
  <pre><code>Qualification Requirements      
  Silver  B2 and R5 and Z3    
  Gold    Z3 and R4 and B3    
  Platinum    (B2 and R6) or (Z3 and B6)
  
  Candidates  Codes   Qualifications
  Fred    R3 Z5 Z3 B6 Platinum
  Joan    Z3 R3 R4 B6 Platinum
  William Z3 B2 R4 R5 Silver
  Tanya   B6 R4 Z3 B3 Gold &amp; Platinum
  Sarah   B5 R4 Z3 B1 No soup for you!
  </code></pre>
  
  <p>The idea is that both the Candidate list (and associated codes) and the Qualification Requirements will be changing over time. I was originally hoping to replace the Qualification Requirement lists with a regex (I think (?=.*B3)(?=.*Z3) or something like that would be equivalent to "B3 and Z3") and then just use regexmatch in a vlookup or something, but it requires some regex features (lookahead) that I believe the Sheets regex tools don't have. There may be another way to do this that just hasn't occurred to me, so all suggestions are welcome!
  Constraints, in case they aren't obvious:
  The Codes lists for each candidate will be unordered, and may fulfill zero or more qualification requirements. It would be good if the qualification requirements are somewhat readable, and they must have and, or, and grouping capability. At least, that's my wish list.</p>
  
  <p>Thanks!</p>