excel - Doing an array formula lookup

Question

I have a list of data like this:

Name   |    Number
Bob    |   300
Joe    |   200
Jane   |   400
Sisqo  |   450
Jill   |   500

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  

What I have is:

{=iferror(INDEX($A$2:$B$6, SMALL(IF($B$2:$B$6 >= 400, $B$2:$B$6,), ROW(1:1)), 1), "")}

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.

At this point the row of the lowest number at 400 or below should be found.

So then the index function should read

index(a2:b6, 3, 1)

for the first one.Except I'm only getting

Name
Bob Bob

So where is the error?

Answer 1

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)),"")

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.