arrays - Google 表格中的二维查找
问题描述
我正在 Google 表格中进行二维查找,以在工作表上的任何位置查找名称,并在名称出现的同一行的第四列中返回值。名称可能出现在多个位置,我想显示每个实例的结果,因此这需要作为数组输出。
以下是我目前拥有的;有没有更优雅的方法来做到这一点?
如果可能,我想避免为此使用 Apps 脚本(另一位用户提出了类似的问题,这就是建议)。
=iferror(if(E3 = "", "", index(unique(query('TransposedT&O'!$A$1:$BB$70, "select D where
E contains '" & E3 & "' or
F contains '" & E3 & "' or
G contains '" & E3 & "' or
H contains '" & E3 & "' or
I contains '" & E3 & "' or
J contains '" & E3 & "' or
K contains '" & E3 & "' or
L contains '" & E3 & "' or
M contains '" & E3 & "' or
N contains '" & E3 & "' or
O contains '" & E3 & "' or
P contains '" & E3 & "' or
Q contains '" & E3 & "' or
R contains '" & E3 & "' or
S contains '" & E3 & "' or
T contains '" & E3 & "' or
U contains '" & E3 & "' or
V contains '" & E3 & "' or
Q contains '" & E3 & "' or
R contains '" & E3 & "' or
S contains '" & E3 & "' or
T contains '" & E3 & "' or
U contains '" & E3 & "' or
V contains '" & E3 & "' or
W contains '" & E3 & "' or
X contains '" & E3 & "' or
Y contains '" & E3 & "' or
Z contains '" & E3 & "' or
AA contains '" & E3 & "' or
AB contains '" & E3 & "' or
AC contains '" & E3 & "' or
AD contains '" & E3 & "' or
AE contains '" & E3 & "' or
AF contains '" & E3 & "' or
AG contains '" & E3 & "' or
AH contains '" & E3 & "' or
AI contains '" & E3 & "' or
AJ contains '" & E3 & "' or
AK contains '" & E3 & "' or
AL contains '" & E3 & "' or
AM contains '" & E3 & "' or
AN contains '" & E3 & "' or
AO contains '" & E3 & "' or
AP contains '" & E3 & "' or
AQ contains '" & E3 & "' or
AR contains '" & E3 & "' or
AS contains '" & E3 & "' or
AT contains '" & E3 & "' or
AU contains '" & E3 & "' or
AV contains '" & E3 & "' or
AQ contains '" & E3 & "' or
AR contains '" & E3 & "' or
AS contains '" & E3 & "' or
AT contains '" & E3 & "' or
AV contains '" & E3 & "' or
AW contains '" & E3 & "' or
AX contains '" & E3 & "' or
AY contains '" & E3 & "' or
AZ contains '" & E3 & "' or
BA contains '" & E3 & "' or
BB contains '" & E3 & "'"
, 0)), 1, 1)), "")
解决方案
利用:
=ARRAYFORMULA(IFERROR(IF(E3="",, INDEX(UNIQUE(QUERY({'TransposedT&O'!$A$1:$BB$70},
"select Col4 where "&TEXTJOIN(" or ", 1, "Col"&TRANSPOSE(COLUMN(E:BB))&
" contains '"&E3&"'"), 0)), 1, 1)), ))
推荐阅读
- java - 如何通过 Selenium Java 将鼠标悬停在具有动态 xpath 的不同图像上
- python - 如何在python中识别维基百科类别
- node.js - 在 res.render() 中指定布局,同时为其提供数据
- python-3.x - 如何通过python从带有条件的pandas数据框中删除第二个连续/出现的重复行?
- assembly - 面对两个函数中的语句?
- javascript - 如何在 Vue.js 中使用 dom-to-image 或 html2canvas?
- python - 如何绘制均值为 0 的协方差误差椭圆?
- firebase - Firebase - 权限不足,即使我是项目所有者
- visual-studio-code - VS 代码片段 | 占位符 + 变换
- python - 未知?塑造 keras 深度学习