执行数组公式查找 [英] Doing an array formula lookup
问题描述
我有一个这样的数据列表:
I have a list of data like this:
Name | Number
Bob | 300
Joe | 200
Jane | 400
Sisqo | 450
Jill | 500
这里有大约62行,所有数字都可以不同,有些重复。我的目标是基本上添加一个列,其中包含所有400以上的人员列表。所以它看起来像这样:
There's about 62 rows of this, all numbers which can be different, some repeated. My goal is to basically add a column which contains all the list of people who are at 400 or above. So that it looks like this:
Name
Jane
Sisqo
Jill
我所拥有的是:
{=iferror(INDEX($A$2:$B$6, SMALL(IF($B$2:$B$6 >= 400, $B$2:$B$6,), ROW(1:1)), 1), "")}
我想像这样做:
1)小函数正在查找范围b2:b6,因为这是一个数组公式,查看B2中的每个单元格:B6,看看它是否大于或等于400.
2)我不知道正是如何,但是我希望行函数只有在正在考虑的单元格中的值大于或等于400时才发现b2:b6的数组。如果没有找到任何东西,没有任何反应。
3)然后运行比较小(b2:b6,1)为该范围中的最低值。然后当复制下来,因为我使用row()函数,它会很小(b2:b6,2),小(b2:b6,3)等等。
What I imagine this is doing: 1) The small function is looking in range b2:b6, and because this is an array formula, looks at each cell in B2:B6 to see if its greater or equal to 400. 2) I don't know exactly how, but I hope that the row function is finding the array of b2:b6 only if the value in the cell under consideration is greater than or equal to 400. If it doesn't find anything, nothing happens. 3) Then it runs the comparison small(b2:b6, 1) for the lowest value in that range. Then when copied down, because I'm using the row() function, it will be small(b2:b6, 2), small(b2:b6, 3) and so on.
此时应找到最低号码在400以下的行。
At this point the row of the lowest number at 400 or below should be found.
所以索引函数应该读取
index(a2:b6, 3, 1)
为第一个。除了我只得到
for the first one.Except I'm only getting
名称
Bob
Bob
Name
Bob
Bob
那么错误在哪里?
推荐答案
我更喜欢使用MATCH数组形式:
I prefer to use MATCH in an Array form:
=IFERROR(INDEX($A$2:$A$6,MATCH(1,(COUNTIFS($E$1:E1,$A$2:$A$6)=0)*($B$2:$B$6>=400),0)),"")
作为数组公式,在退出编辑模式时,必须使用Ctrl-shift-Enter而不是Enter进行确认。如果正确完成,Excel将在公式周围放置 {}
。
Being an array formula it must be confirmed with Ctrl-shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {}
around the formula.
这篇关于执行数组公式查找的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!