return multiple cells meeting a condition

[QC-Dude]

How do I construct a formula to return multiple entries meeting a criteria?
I want the formula to return a vertical list of names where grade=2 or higher.

Example:
NAME GRADE
Mike 3
Scott 2
Mary 1
Larry 2

 

[T. Valko]

Try this array formula** :

Name = named range for the name column
Grade = named range for the grade column

Assume you want the list to start in cell D1.

Enter this array formula** in D1:

=IF(ROWS(D$11)<=COUNTIF(Grade,">=2"),INDEX(Name,SMALL(IF(Grade>=2,ROW(Name)-MIN(ROW(Name))+1),ROWS(D$11))),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)