title: Returning ArrayFormula from multiple sheets, in the same cell
body: <p>I have two Google sheets with different Marvel characters (column A) and their corresponding home universe (column C). </p>
  
  <p><strong>Sheet1</strong></p>
  
  <pre><code>| Heroes            | Count         | Universe           |
  |:------------------|--------------:|:------------------:|
  | Spider Ham        | 365           | Earth-8311         |
  | Iron Man          | 467           | Earth-616          |
  | Hulk Bunny        | 24            | Earth-8311         |
  </code></pre>
  
  <p><strong>Sheet2</strong></p>
  
  <pre><code>| Heroes            | Count         | Universe           |
  |:------------------|--------------:|:------------------:|
  | Spider Ham        | 234           | Earth-8311         |
  | Iron Man          | 998           | Earth-616          |
  | May Porker        | 11            | Earth-8311         |
  </code></pre>
  
  <p>In <strong>Sheet3</strong> this formula in each row <code>=ArrayFormula(TEXTJOIN(", ";1;REPT(Sheet1!A:A;1*(Sheet1!C:C=A$2))))</code> (placed in column B) grabs the data from Sheet1 and return in like this:</p>
  
  <pre><code>| Universe         | Heroes                              |
  |:-----------------|------------------------------------:|
  | Earth-8311       | Spider Ham, Hulk Bunny              |
  | Earth-616        | Iron Man                            |
  </code></pre>
  
  <p>Which is great. But I also want combine the data from Sheet2 in the formula - so Sheet3 looks like this (without duplicates):</p>
  
  <pre><code>| Universe         | Heroes                              |
  |:-----------------|------------------------------------:|
  | Earth-8311       | Spider Ham, Hulk Bunny, May Porker  |
  | Earth-616        | Iron Man                            |
  </code></pre>
  
  <p>I want to modify the ArrayFormula to search in Sheet1 AND Sheet2 - is this possible to do in a single Spreadsheet-formula? </p>
  
  <p>Tried <code>=ArrayFormula(TEXTJOIN(", ";1;REPT(Sheet1:Sheet2!A:A;1*(Sheet1Sheet2!C:C=A$2))))</code> but it only returns <code>#NAME?</code></p>