Returning ArrayFormula from multiple sheets, in the same cell

Question

I have two Google sheets with different Marvel characters (column A) and their corresponding home universe (column C).

Sheet1

| Heroes            | Count         | Universe           |
|:------------------|--------------:|:------------------:|
| Spider Ham        | 365           | Earth-8311         |
| Iron Man          | 467           | Earth-616          |
| Hulk Bunny        | 24            | Earth-8311         |

Sheet2

| Heroes            | Count         | Universe           |
|:------------------|--------------:|:------------------:|
| Spider Ham        | 234           | Earth-8311         |
| Iron Man          | 998           | Earth-616          |
| May Porker        | 11            | Earth-8311         |

In Sheet3 this formula in each row =ArrayFormula(TEXTJOIN(", ";1;REPT(Sheet1!A:A;1*(Sheet1!C:C=A$2)))) (placed in column B) grabs the data from Sheet1 and return in like this:

| Universe         | Heroes                              |
|:-----------------|------------------------------------:|
| Earth-8311       | Spider Ham, Hulk Bunny              |
| Earth-616        | Iron Man                            |

Which is great. But I also want combine the data from Sheet2 in the formula - so Sheet3 looks like this (without duplicates):

| Universe         | Heroes                              |
|:-----------------|------------------------------------:|
| Earth-8311       | Spider Ham, Hulk Bunny, May Porker  |
| Earth-616        | Iron Man                            |

I want to modify the ArrayFormula to search in Sheet1 AND Sheet2 - is this possible to do in a single Spreadsheet-formula?

Tried =ArrayFormula(TEXTJOIN(", ";1;REPT(Sheet1:Sheet2!A:A;1*(Sheet1Sheet2!C:C=A$2)))) but it only returns #NAME?

Answer 1

Please try:

=ArrayFormula(TEXTJOIN(", ",1,UNIQUE(filter({Sheet1!A:A;Sheet2!A:A},{Sheet1!C:C;Sheet2!C:C}=A2))))

Used arrays {} in Google Sheets, and filter formula. Unique is to get rid of duplicates.