需要在目标范围不在固定位置的情况下创建数组公式

Question

目前，我在名为Data的工作表上的连续行中使用以下公式

{=IFERROR(SMALL(IF($Q$2=DMS!$I$1:$I$99999,ROW(DMS!$I$1:$I$99999)-ROW(DMS!$I$1)+1),1),"")}
{=IFERROR(SMALL(IF($Q$2=DMS!$I$1:$I$99999,ROW(DMS!$I$1:$I$99999)-ROW(DMS!$I$1)+1),2),"")}
{=IFERROR(SMALL(IF($Q$2=DMS!$I$1:$I$99999,ROW(DMS!$I$1:$I$99999)-ROW(DMS!$I$1)+1),3),"")}

..。

对于总共30行，以确定工作表DMS列I中与我的工作表“数据”单元格Q2中的值匹配的前30行。

当搜索范围不再是固定位置时，我试图找到一种重新创建上述数组的方法，或者是使用数组的替代解决方案。本质上，第一栏中的内容，我可以在明天和第二天在J栏中，然后在接下来的3天内返回给I。我将能够通过列标题知道它在哪里，所以我确实有一个静态的参照点，即使它不是在一个固定的列中。

Answer 1

试试这个公式。它在列A到Z的第一行中查找文本为"TheKey“的列，然后对查找列中的值执行匹配。

在Excel 365中，您只需点击enter，在早期版本中，您需要使用Ctrl+Shift+Enter

=IFERROR(SMALL(IF($Q$2=INDEX(dms!$A$1:$Z$9999,1,MATCH("TheKey",dms!$A$1:$Z$1,0)):INDEX(dms!$A$1:$Z$9999,9999,MATCH("TheKey",dms!$A$1:$Z$1,0)),ROW($A$1:$A$9999)),ROW(A1)),"")

在Excel 365中，通过应用新的Let()函数并重用结果，可以避免匹配函数的重复。如下所示：

=LET(myColumn,MATCH("TheKey",dms!$A$1:$Z$1,0),IFERROR(SMALL(IF($Q$2=INDEX(dms!$A$1:$Z$9999,1,myColumn):INDEX(dms!$A$1:$Z$9999,9999,myColumn),ROW($A$1:$A$9999)),ROW(A1)),""))

我删除了-ROW(DMS!$I$1)+1，因为它没有任何作用，真的。只需减去1，然后再加1。

我还将后续行(1、2、3等)的硬编码数字替换为Row(A1)，这将在公式的第一行中计算为1，复制后将变为2、3、4等，而不必调整每一行的公式。

Answer 2

您需要组合几个高级功能。首先，您需要定位那个标头，因为它是您唯一的静态点。

​

​

您可以使用以下方法定位静态标头：

=CELL("address";INDEX(DMS!$A$1:$M$1;;MATCH("STATIC HEADER";DMS!$A$1:$M$1;0)))

在本例中，它将返回以下文本：[Libro1]DMS!$I$1

这是通往细胞的完整道路。

现在，您需要将该字符串与偏移量组合起来，以获得对该列的引用，并获得大约99999行数据。这里的ISsue偏移量不适用于与活动工作表不同的工作表，因此您需要首先将其与间接工作表结合起来，以获得对工作表DMS中范围的引用。

间接函数 偏移函数

因此，下面这样的公式将返回1列和99999单元格的引用，其中静态标头是：

OFFSET(INDIRECT(CELL("address";INDEX(DMS!$A$1:$M$1;;MATCH("STATIC HEADER";DMS!$A$1:$M$1;0))));0;0;99999;1)

所以现在你可以把所有这些组合成一个非常大的公式：

=IFERROR(SMALL(IF($Q$2=OFFSET(INDIRECT(CELL("address";INDEX(DMS!$A$1:$M$1;;MATCH("STATIC HEADER";DMS!$A$1:$M$1;0))));0;0;99999;1);ROW(OFFSET(INDIRECT(CELL("address";INDEX(DMS!$A$1:$M$1;;MATCH("STATIC HEADER";DMS!$A$1:$M$1;0))));0;0;99999;1)));ROW()-5);"")

注意，我删除了ROW(DMS!$I$1)+1，因为它总是等于0，所以不需要。我在小函数中添加了ROW()-5)部分，这样我就可以向下拖动(我正在第6行进行测试，然后向下拖放。不需要你手动输入的1，2，3)。根据你的需要调整这个部分。

​

​

注意，如果STATIC HEADER位于不同的列中，则公式仍然有效。

​

​

我上传了一个文件，如果您想知道它是如何工作的，请执行我的Gdrive，因为复制这么多函数可能会很疯狂。

Hu1TKLXhTUGVq8tN67eHQKtVb/view?usp=共享