我获得了目录树布局中的数据(见图)
基本上,我想使用一个查找代码作为参考点,并让 Excel 在相应的列中向上查找以显示“父目录”。
目前我有一个索引,可以准确地告诉我代码是什么,但我想设置一个查找工具(见下图中的下表),可以一次显示所有级别。在图片中,我使用公式输入了我想要返回的值。
因此,如果我输入 4 级代码,我希望使用返回 4、3、2 和 1 级程序的公式。对于 3 级代码,我希望它查找以返回 3、2 和 1 级程序。
有10,000+行数据。
对我来说这似乎很简单......从这一行开始并沿着这一列向上查找直到达到一个值.....但它似乎不是那么简单。
任何帮助/提示都将不胜感激!
-亚历克斯
答案1
正如您所注意到的,Excel 本身并不支持“向上查找”。index()
或等函数vlookup()
非常适合查找第一的匹配,但不是最后的。
假设您的数据位于单元格 A1:F10 中,此公式将从单元格 H2 中输入的代码(D 列)开始,查找 A 列中的最后一个非空白单元格。它可以自动向右填充(以查找其他列)和向下填充(以搜索更多代码)。这是一个数组公式,必须使用 ctrl+shift+enter 进行确认。
=INDEX(A$2:A$10,MAX(IF(NOT(ISBLANK(OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0)))),ROW(OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0))),""))-1)
OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0))
用于动态调整查找数组的大小,使其从 A2 开始,到找到所需代码的行结束。因此,如果您的代码位于 D5,此部分将返回A2:A5
MAX(IF(NOT(ISBLANK(...)),ROW(...),""))
这将返回我们之前找到的范围内最高非空白单元格的行号。
INDEX(A$2:A$10,...-1)
最后,index()
找到我们找到的单元格的实际值。我们减一是因为查找范围从第 2 行开始,而我们使用的行数显然从 1 开始。