return multiple cells meeting a condition
 

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

--
Warm Regards, MikCra

return multiple cells meeting a condition
 

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$1:D1)<=COUNTIF(Grade,"=2"),INDEX(Name, SMALL(IF(Grade=2,ROW(Name)-MIN(ROW(Name))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks

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

--
Biff
Microsoft Excel MVP


"QC-Dude" wrote in message
...
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

--
Warm Regards, MikCra