将公式从列中的分隔值中获取 unqiue 列表与公式相结合，以获取将工作表名称连接到输出的范围

Question

>我在一列中有单元格值，可以通过管道分隔，例如：DR|Border|Yaque del Norte或空或非分隔，例如：Cat

我有一个公式，可以从列中获取所有值的唯一列表(以 not 分隔)

=SORT(UNIQUE(TRANSPOSE(ARRAYFORMULA(TRIM(SPLIT(JOIN("|",Elements!A2:A),"|"))))))

从上面的三个值中，我会得到

Border
Cat
DR
Yaque

我有一个公式可以获取范围Elements!A2:A

indirect("Elements!"&substitute(address(1,match(A1,Elements!1:1,0),4),"1","")&"2:"&substitute(address(1,match(A1,Elements!1:1,0),4),"1",""))

但是当我这样做时，我的输出变成了

Elements!Border
Cat
Elements!DR
Elements!Yaque

所有分隔元素都Elements!添加到其中

如何防止这种情况

谢谢

带有数据和公式的谷歌表格 https://docs.google.com/spreadsheets/d/15tpuRdZUrSfiNapXWUtL2Zt1QOaxtf4T7SG1FwLyi30/edit?usp=sharing

Answer 1

尝试offset()函数，如下所示：

=sort( unique( transpose( trim( 
split( 
join( 
"|", 
offset( Elements!A2:A, 0, match(A1, Elements!A1:D1, 0) - 1 ) 
), 
"|", false, true 
) 
) ) ) )

请参阅"忽略"工作表中的示例电子表格、单元格H2。