从MATCH返回列引用以避免将INDIRECT与命名范围一起使用

Question

TL；DR：我基本上是想获得一个列范围，如'Sheet 1'!$A:$A，其中a是通过将给定单元格的内容与另一个给定单元格引用的表中的1:1范围匹配来获得的，用于动态范围。

在极有可能毫无意义的情况下，这里有一个例子：

参数：A2="LIST"|C2="FirstName"|所需结果：'LIST'!$A:$A

我已经得到了，但是，我不能在公式中使用输出('LIST'！$A:$A)(即创建动态范围)。例如，此处为"LIST"$A： $A包含101个单元格，其中包含值：

V3=NamedFormula='LIST'$A： $A

COUNTA(INDIRECT(V3))=101

CCD_ 7＝1，因为它的计算结果为#VALUE并且这是奇异结果。

在深入研究使用带命名范围的INDIRECT的主题之前(我读过这篇文章，现在仍在克服我困惑的悲伤)，我意识到我的名字有点失控了。我喜欢像个疯狂的科学家一样使用Excel。因此，如果有一个更简单的解决方案来解决我想要做的事情，下面是我的实际任务：

0.我正在构建一个工具来简化从不同数据构建电子邮件地址的过程，该过程需要在没有任何脚本、只有公式的情况下运行。

1。没有强制名称的选项卡将包含一个用户数据库，该数据库的最小值(名字和姓氏OR ID)为and(可能是其他数据列)，没有特定的顺序。工具用户将根据客户端的不同从数据到达的任何位置导入该选项卡，并且只需要将相关的标题复制粘贴到主选项卡，而无需更改此处的任何其他内容以确保数据完整性。

2.主选项卡将有特定的输入字段，工具用户将在其中粘贴导入选项卡的名称以及他们需要的列的标签(例如，列的第一行中包含名字和姓氏的标签)，以及用于构建这些电子邮件地址的域名的输入字段。

3.数据选项卡用于清理和准备电子邮件地址格式的字符串。

4.导出选项卡会弹出一个可以导出到CSV的干净电子邮件地址列表。

"数据"选项卡只有2列可与SUBSTITUTE一起使用，因此例如可以删除撇号，但对重音字母进行规范化(é->e)。我在Names中使用了LAMBDA。问题是把所有的东西都联系起来——把那些命名的范围加入到最终的公式中。

到目前为止我使用的名称(我想使用更少的名称，但我担心测试的特定部分超出了简单的使用范围)：

ALPH={"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"}

标签=LAMBDA(labelname,ADDRESS(2,MATCH(labelname,INDIRECT("'"&PARAMETERS!$A$2&"'!$1:$1"),0),1,1,PARAMETERS!$A$2))

RANGECOL=LAMBDA(labelname,COLUMN(INDIRECT(LABELS(labelname))))

RNCOL=LAMBDA(label,"'"&PARAMETERS!$A$2&"'!$"&INDEX(ALPH,RANGECOL(label))&":$"&INDEX(ALPH,RANGECOL(label)))

我还没有在"数据"选项卡中绑定所有内容-在进一步推进并在所有内容之上使用"数据"标签替换之前，我仍在尝试自动化我的主选项卡。这将是下一步，而不是我目前的重点。但是，对于好奇和感兴趣的人来说，在"数据"选项卡上，我使用了我在ablebits上发现的一些神奇的东西=]

所以，现在如果我使用静态LIST!A:A的偏移范围，它会起作用：

=IF($C$2<>"",LOWER(INDEX(OFFSET(INDIRECT(ADDRESS(2,MATCH($C$2,INDIRECT("'"&$A$2&"'!$1:$1"),0),1,1,$A$2)),0,0,COUNTA(LIST!A:A)-1,1),ROW())),"") &IF($C$3<>"","."&LOWER(INDEX(OFFSET(INDIRECT(ADDRESS(2,MATCH($C$3,INDIRECT("'"&$A$2&"'!$1:$1"),0),1,1,$A$2)),0,0,COUNTA(LIST!A:A)-1,1),ROW())),"") &"@"&$C$4

但当我尝试使用动态RNCOL($C$3)时，它并没有：

=IF($C$2<>"",LOWER(INDEX(OFFSET(INDIRECT(LABELS($C$2)),0,0,COUNTA(INDIRECT(RNCOL($C$2)))-1,1),ROW())),"") &IF($C$3<>"","."&LOWER(INDEX(OFFSET(INDIRECT(LABELS($C$3)),0,0,COUNTA(INDIRECT(RNCOL($C$3)))-1,1),ROW())),"") &"@"&$C$4

这只是给出了#REF，评估显示了从INDIRECT(RNCOL($C$3))开始的离题，相当于#VALUE。

我开始在这里看到替身了，但我对Excel永恒而完全正常的热爱阻止了我下班回家，因为我离兔子洞太远了，不能让我的痴迷在这里消失。

有什么关于这如何运作的建议吗？注意-提供的表单中的所有名称都是由在线假名生成器生成的，这里没有实际的用户数据#GDPR

提前感谢<3

测试表可通过Google Drive获得。

Answer 1

由于多种原因，您当前的设置不好，在我看来，需要进行全面检修，其范围超出了本网站的响应范围。

至于对当前问题的"快速修复"，E1中的公式当前返回错误的原因是，正如您通过Evaluate formula工具逐步了解的那样，零件

COUNTA(INDIRECT(RNCOL($C$2)))-1

正在解析为

COUNTA(INDIRECT({"'LIST'!$A:$A"}))-1

这是而不是与相同

COUNTA(INDIRECT("'LIST'!$A:$A"))-1

传递给CCD_ 18的值在前者中是数组，但在后者中不是。尽管INDIRECT可以接受数组，但它仅在某些结构中与其他合适的函数结合使用；在这里它只会出错。

它返回数组的原因是RNCOL($C$2)返回数组，这是因为该函数被定义为

=LAMBDA(label,"'"&PARAMETERS!$A$2&"'!$"&INDEX(ALPH,RANGECOL(label))&":$"&INDEX(ALPH,RANGECOL(label)))

并且，由于RANGECOL($C$2)在这里解析为1，因此以上等价于

"'PARAMETERS!$A$2'!$"&INDEX(ALPH,1)&":$"&INDEX(ALPH,1)

这里，因为您从INDEX中省略了column_num参数

INDEX(ALPH,1)

正在解析为

{"A"}

它是一个数组(尽管它包含一个值)，在技术上与不同

"A"

在大多数情况下，这不是一个问题。因此，在索引一维数组时，几乎总是不需要将row_num和column_num参数同时传递给INDEX。然而，在这里，它很重要。

您可以通过显式包含column_num参数来解决此问题，即将RNCOL重新定义为

=LAMBDA(label,"'"&PARAMETERS!$A$2&"'!$"&INDEX(ALPH,RANGECOL(label),1)&":$"&INDEX(ALPH,RANGECOL(label),1))