进行数组公式查找 [英] 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 时，row 函数才能找到 b2:b6 的数组.如果它没有找到任何东西，没发生什么事.3) 然后它运行该范围内的最小值的比较 small(b2:b6, 1) .然后复制下来的时候，因为我用的是row()函数，会是small(b2:b6, 2), small(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

姓名
鲍勃鲍勃

那么错误在哪里?

推荐答案

我更喜欢以数组形式使用 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屋！