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.
--
Archie

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

Biff

"Teethless mama" wrote in message
...
I'm not sure what you are after. Here is my guest

Assuming your data in A1:B4 in Sheet 1
A1:A4 (define name "Color")
B1:B3 (define name "Type") of course no quote

In Sheet2
A1: hold Green

B1:
=IF(ISERR(SMALL(IF(Color=Sheet2!$A$1,ROW(INDIRECT( "1:"&ROWS(Color)))),ROWS($1:1))),"",INDEX(Type,SMA LL(IF(Color=Sheet2!$A$1,ROW(INDIRECT("1:"&ROWS(Col or)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Drag Fill Handle to copy down from B1 as far as required



"Archie999" wrote:

My source table is actually in a separate worksheet and I am using a
named
cell to populate the Match criteria, so a sort of form. AutoFilter would
still be a bit complicated due to the number of unique "categories"
assorted
with Column A - Colors.
--
Archie


"Teethless mama" wrote:

How about Auto Filter


"Archie999" wrote:

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.
--
Archie