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?

Max Makhrov · Accepted Answer · 2017-11-13 07:05:08Z

2

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.

answered Nov 13, 2017 at 7:05

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Liu Kang Over a year ago

This works flawlessly: =ArrayFormula(TEXTJOIN(", ";1;UNIQUE(filter({Sheet1!A:A;Sheet2!A:A};{Sheet1!C:C;Sheet2!C:C}=A2)))) - just had to change , to ; since I have some regional settings - thank you so much!

Collectives™ on Stack Overflow

Returning ArrayFormula from multiple sheets, in the same cell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related