跨单元格搜索并返回不同单元格的值

Question

我需要根据来自四个不同列的下拉值来引用一个单元格。

本质上，我正在尝试编译来自不同选项卡的数据摘要列表。每个选项卡的布局相同，但是在这个新的摘要中，我需要根据 D2:G2 中的值从 A2 中提取数据。我尝试了很多不同的公式，但都无济于事。我最接近的是：

=INDEX(Assessments!D2:G2,MATCH(TRUE,A1,Assessments!A2),0)

这将返回 #VALUE 消息，指出参数 3 需要数字值，但“NAME”是文本，不能强制转换为数字。

和

=vlookup($A$1,{Assessments!D2:G2,Assessments!A2},1,0)

这将返回 A1 中的值，但我实际上需要它返回评估中的文本！A2

数据集：

姓名	发射	描述	标签	标签	标签	标签
评估标题 A	rd_28	测试	开导	拥抱	教育
评估标题 B	rd_28	测试	提高	拥抱
评估标题 C	rd_28	测试	提高	开导	拥抱	教育
评估标题 D	rd_28	测试	拥抱
评估标题 E	rd_28	测试	拥抱	教育
评估标题 F	rd_28	测试	开导	拥抱	教育
评估标题 G	rd_28	测试	提高	拥抱
评估标题 H	rd_28	测试	提高	开导	拥抱	教育
评估标题 I	rd_28	测试	开导	拥抱
评估标题 J	rd_28	测试	提高
评估标题 K	rd_28	测试	拥抱	教育
评估标题 L	rd_28	测试	提高

理想情况下，此信息将被拉入具有以下列标题的新工作表中：（A1 = 增强）

提高	开导	拥抱	教育

一旦我获得线性格式的所有评估数据，我就需要再次使用公式从其他选项卡中提取数据，例如：

姓名	发射	描述	标签	标签	标签	标签
文档标题 A	rd_1	测试	开导	拥抱
文档标题 B	rd_1	测试	提高	拥抱	教育
文档标题 C	rd_1	测试	提高	开导	拥抱
文档标题 D	rd_1	测试	拥抱	教育
文档标题 E	rd_1	测试	拥抱	教育	提高
文档标题 F	rd_1	测试	开导	拥抱	教育
文档标题 G	rd_1	测试	提高	拥抱
文档标题 H	rd_1	测试	提高	开导	拥抱	教育
文档标题 I	rd_1	测试	开导	拥抱	提高
文档标题 J	rd_1	测试	教育
文档标题 K	rd_1	测试	拥抱	教育
文档标题 L	rd_1	测试	提高

Answer 1

所有标准查找功能均不起作用，因为

1. 您需要在多个标签列中搜索相同的值
2. 如果有多个匹配项（而不仅仅是第一个），则需要返回多个行号

要实现这一点，您需要使用数组函数。新的 Office 365 / Google Sheets 数组函数将非常有用：

=TEXTSPLIT(TEXTJOIN(",",TRUE,IFERROR(INDEX(Assessments!$A$2:$A$21,BYROW(A$1=Assessments!$D$2:$G$21,LAMBDA(x,2*OR(x)-1))*ROW($A$1:$A$20)),"")),,",")

解释：

A$1=Assessments!$D$2:$G$21

返回与标签头匹配的布尔矩阵。

BYROW(..., LAMBDA(x, 2*OR(x)-1)

逐行获取此矩阵，然后将行元素进行“或”操作（即该行中有匹配项），并返回一个列向量，如果该行中有匹配项，则为 1，否则为 -1。

BYROW(...)*ROW($A$1:$A$20)

将 -1 和 +1 转换为行号，可用作评估名称的索引（负数将返回错误）

IFERROR(INDEX(..., BYROW(...)*ROW(...)), "")

返回评估名称的列向量，其中没有匹配项的行中为空“”。

TEXTSPIT(TEXTJOIN(...)...)

首先将列向量连接在一起作为文本，用逗号分隔并忽略空的不匹配项。然后将文本拆分回较短的列向量，不只包含匹配的名称。