title: Excel filter table values by multiple conditions
body: <p>I have 2 tables:</p>
  <ul>
  <li>one that contains a set of properties: their unique ID, offer type and reference points (for localization) - named Table_Prop</li>
  <li>one that contains requests from clients, that are searching for a property: their unique ID, offer type and the reference points they are interested in. - named Table_Req</li>
  </ul>
  <p>PROPERTIES TABLE</p>
  <pre><code>Property ID Offer type  Reference points
        P1        sell        RP1, RP2, RP3
        P2        sell        RP1, RP4
        P3        rent        RP5
        P4        rent        RP5, RP6
        P5        sell        RP3, RP2
  </code></pre>
  <p>REQUESTS TABLE</p>
  <pre><code>Request ID  Offer type  Reference point
      R1         sell          RP1
      R2         rent          RP6
      R3         sell          RP3
  </code></pre>
  <p>All I want is to filter for each request the ID of the matching properties. So I did with this formula:</p>
  <pre><code>=TRANSPOSE(FILTER(Table_Prop[Property ID],
  (ISNUMBER(SEARCH(Tabel_Req[@[Offer type]], Table_Prop[Offer type], 1))=TRUE)*
  (ISNUMBER(SEARCH(Tabel_Req[@[Reference point]], Table_Prop[Reference points]))=TRUE), &quot;NO MATCHES&quot;))
  </code></pre>
  <p>This works just fine, for each requests, it returns:</p>
  <pre><code>R1: P1      P2
  R2: P4
  R3: P1      P5
  </code></pre>
  <p>but 1 client has one or more reference points he is interested in, so the requests table would look like this:</p>
  <pre><code>Request ID  Offer type  Reference points
      R1         sell          RP1, RP2
      R2         rent          RP6, RP3
      R3         sell          RP3, RP4
  </code></pre>
  <p>Basically, now, I need a formula that returns all the matching ID Properties that has one of the request's reference points included in the property's reference points.</p>
  <p>It should return this:</p>
  <pre><code>R1: P1       P2        P5
  R2: P4
  R3: P1       P2        P5
  </code></pre>
  <p>I found this formula that splits the cell value by &quot;,&quot; separator:</p>
  <pre><code>=FILTERXML(&quot;&lt;t&gt;&lt;s&gt;&quot;&amp;SUBSTITUTE(Tabel_Req[@[Reference points]],&quot;,&quot;,&quot;&lt;/s&gt;&lt;s&gt;&quot;)&amp;&quot;&lt;/s&gt;&lt;/t&gt;&quot;,&quot;//s&quot;)
  </code></pre>
  <p>and I tried this formula:</p>
  <pre><code>=FILTER(Table_Prop[Property ID], OR(COUNTIF(Table_Prop[@Reference points], &quot;*&quot;&amp;FILTERXML(&quot;&lt;t&gt;&lt;s&gt;&quot;&amp;SUBSTITUTE(Tabel_Req[@[Reference points]],&quot;,&quot;,&quot;&lt;/s&gt;&lt;s&gt;&quot;)&amp;&quot;&lt;/s&gt;&lt;/t&gt;&quot;,&quot;//s&quot;)&amp;&quot;*&quot;))=TRUE, &quot;NO MATCHES&quot;)
  </code></pre>
  <p>but as I expected, it doesn't work.</p>
  <p>How can I reach my goal, are there any workarounds? Any help is appreciated.</p>
  <p>Note: I wouldn't like to use VBA, just a formula in a cell that returns a list of matching properties.</p>