表一
| Heroes | Count | Universe |
|:------------------|--------------:|:------------------:|
| Spider Ham | 365 | Earth-8311 |
| Iron Man | 467 | Earth-616 |
| Hulk Bunny | 24 | Earth-8311 |
表2
| Heroes | Count | Universe |
|:------------------|--------------:|:------------------:|
| Spider Ham | 234 | Earth-8311 |
| Iron Man | 998 | Earth-616 |
| May Porker | 11 | Earth-8311 |
在里面
表3
每行中的公式
=ArrayFormula(TEXTJOIN(", ";1;REPT(Sheet1!A:A;1*(Sheet1!C:C=A$2))))
(位于B列)从表1中获取数据,并按如下方式返回:
| Universe | Heroes |
|:-----------------|------------------------------------:|
| Earth-8311 | Spider Ham, Hulk Bunny |
| Earth-616 | Iron Man |
这太棒了。但我也希望将来自表2的数据合并到公式中-因此表3看起来像这样(没有重复项):
| Universe | Heroes |
|:-----------------|------------------------------------:|
| Earth-8311 | Spider Ham, Hulk Bunny, May Porker |
| Earth-616 | Iron Man |
我想修改ArrayFormula,以便在Sheet1和Sheet2中搜索-这可以在单个电子表格公式中执行吗?
尝试
=ArrayFormula(TEXTJOIN(", ";1;REPT(Sheet1:Sheet2!A:A;1*(Sheet1Sheet2!C:C=A$2))))
但它只会回来
#NAME?
