title: Excel how to find all rows matching elements from a comma-separated list without VBA
body: <p>Here is my problem, I have a table with two columns: product references and corresponding notice ids:</p>
  <pre><code> | A       | B      | C      | D      |
  ---------------------------------------
  1| Product | Notice |        |        |
  2| p1      | n1     |        |        |
  3| p2      | n2     |        |        |
  4| p3      | n3     |        |        |
  5|         |        |        |        |
  6|         |        | p1, p3 | =...   |
  </code></pre>
  <p>(<strong>edit:</strong> in my real life application, columns 'product references' and 'notice ids' are not alongside but separated by other columns)</p>
  <p>In another cell (e.g. C6), I have a comma separated list of product references, let's say <code>p1, p3</code> and I need a formula to output the corresponding notice ids, i.e. <code>n1, n3</code> in this case, in cell D6.</p>
  <p><strong>Important:</strong> For different reasons, I <strong>cannot use VBA</strong>, I need a standard excel array formula.</p>
  <p>Here is what I can do at the moment:</p>
  <ol>
  <li><p>with the <code>FILTERXML</code> function, I can split the comma-separated list into an array: <code>FILTERXML(&quot;&lt;t&gt;&lt;s&gt;&quot; &amp; SUBSTITUTE(C6, &quot;, &quot;, &quot;&lt;/s&gt;&lt;s&gt;&quot;) &amp; &quot;&lt;/s&gt;&lt;/t&gt;&quot;, &quot;//s&quot;)</code></p>
  </li>
  <li><p>with the <code>TEXTJOIN</code> function, I can merge an array into a string.</p>
  </li>
  <li><p>I can extract a single match with a combination of <code>INDEX</code> and <code>MATCH</code> functions, e.g.:</p>
  </li>
  </ol>
  <pre><code>=IF(ISERROR(MATCH(&quot;p3&quot;; A:A; 0)); &quot;not found&quot;; INDEX(B:B; MATCH(&quot;p3&quot;; A:A; 0)))
  </code></pre>
  <p>(which is not useful for me, since again the references in column A are unique)</p>
  <p>(By the way, I don't know if there is a better way to handle error raised by <code>MATCH</code> when no match is found)</p>
  <ol start="4">
  <li>I can extract and join elements of column B corresponding to multiple matches to a <strong>single reference</strong> in column A with (array formula activated with Ctrl+Shift+Enter):</li>
  </ol>
  <pre><code>{=TEXTJOIN(&quot;, &quot;; TRUE; IF(A:A=&quot;p2&quot;; B:B; &quot;&quot;))}
  </code></pre>
  <p>(which is not useful for me, since again the references in column A are unique)</p>
  <p><strong>In summary:</strong> I can find and merge multiple matches to a single reference, but I cannot find and merge single unique match to multiple references (what I want to do).</p>
  <hr />
  <h2>Failed attempts</h2>
  <p>I tried to mix the previous formulae in different ways to get what I want, but all failed with an error.</p>
  <ul>
  <li>Combining 1, 2 and 4 (using <code>OR</code> on boolean array of matches):</li>
  </ul>
  <pre><code>{=TEXTJOIN(&quot;, &quot;; TRUE; IF(OR(A:A=FILTERXML(&quot;&lt;t&gt;&lt;s&gt;&quot; &amp; SUBSTITUTE(C6, &quot;, &quot;, &quot;&lt;/s&gt;&lt;s&gt;&quot;) &amp; &quot;&lt;/s&gt;&lt;/t&gt;&quot;, &quot;//s&quot;)); B:B; &quot;&quot;))}
  </code></pre>
  <p>or (using <code>SUM</code> on boolean array of matches):</p>
  <pre><code>{=TEXTJOIN(&quot;, &quot;; TRUE; IF(SUM(A:A=FILTERXML(&quot;&lt;t&gt;&lt;s&gt;&quot; &amp; SUBSTITUTE(C6, &quot;, &quot;, &quot;&lt;/s&gt;&lt;s&gt;&quot;) &amp; &quot;&lt;/s&gt;&lt;/t&gt;&quot;, &quot;//s&quot;)); B:B; &quot;&quot;))}
  </code></pre>
  <p>Here, I am not sure how to handle the different arrays that are considered in the <code>IF</code> (column A and list of references given by <code>FILTERXML</code>).</p>
  <ul>
  <li>Combining 1, 2 and 3:</li>
  </ul>
  <pre><code>{=TEXTJOIN(&quot;, &quot;; TRUE; INDEX(B:B; MATCH(FILTERXML(&quot;&lt;t&gt;&lt;s&gt;&quot; &amp; SUBSTITUTE(C6, &quot;, &quot;, &quot;&lt;/s&gt;&lt;s&gt;&quot;) &amp; &quot;&lt;/s&gt;&lt;/t&gt;&quot;, &quot;//s&quot;); A:A; 0)))}
  </code></pre>
  <p>Here, I am not sure how to handle (i) again the different arrays that are considered (column A and list of references given by <code>FILTERXML</code>), (ii) the error raised by <code>MATCH</code> when no match is found, (iii) the array references passed to <code>INDEX</code> function.</p>