Obtaining Multiple Results Using Index/Match Functions

[Archie999]

I have a small table similar to the following:
Color Object
A B
1 Green Apple
2 Yellow Banana
3 Red Stop Sign
4 Green Grass

=INDEX(A1:B4,MATCH("Green",A1:A4,0),2) --> (Apple)
INDEX/MATCH formula works perfectly to obtain the first matching record, but
how can I create a list (each matching item in its own cell in a column) of
all matching records, ie all objects that are Green. Is there some sort of
Exclude function that will allow me to use the same formula without producing
the same result as the cell above it? A Pivot Table is overkill for what I
am trying to do.

 

[T. Valko]

=IF(ROWS($1:1)<=COUNTIF(Color,A$1),INDEX(Type,SMALL(IF(Color=A$1,ROW(Color)-MIN(ROW(Color))+1),ROWS($1:1))),"")

Biff