Index function using multiple values in one cell
 

I have data that I have been categorizing into multiple categories.
What I would like to do is put all the of category types in one cell
(separated by a comma) that can be assigned to that specific data. I
have the Index function working right now that matches the value I have
and gives me the data that corresponds to that. See example below.

=INDEX($D$1:$D$1000,SMALL(IF($A$1:$A$1000=K$1,ROW( $A$1:$A$1000)-ROW(A$1)+1),ROWS($1:3)))

Data Example:

A B
C, D, E, F (Spring, Summer, Fall, Winter)

Spring, Summer Beaches
Fall Leaves
Winter Snowboarding
Winter, Spring Cold Weather

The results would be as follows (where the index function above (k$1
would change to l$1 if I dragged it across - the values in those cells
would be spring, summer, fall, winter)

Spring Summer Fall
Winter
Beaches Beaches Leaves
Snowboarding
Cold Weater
Cold Weather

Like I said, I can get the function to work if there is only one value
in a cell in column A, but I would like to have multiple values listed
so I don't need to make a bigger sheet.

Please help if you can. Thanks.

Index function using multiple values in one cell
 

Hi!

Try changing this:

SMALL(IF($A$1:$A$1000=K$1

To:

SMALL(IF(ISNUMBER(SEARCH(K$1,$A$1:$A$100))

Biff

wrote in message
oups.com...
I have data that I have been categorizing into multiple categories.
What I would like to do is put all the of category types in one cell
(separated by a comma) that can be assigned to that specific data. I
have the Index function working right now that matches the value I have
and gives me the data that corresponds to that. See example below.

=INDEX($D$1:$D$1000,SMALL(IF($A$1:$A$1000=K$1,ROW( $A$1:$A$1000)-ROW(A$1)+1),ROWS($1:3)))

Data Example:

A B
C, D, E, F (Spring, Summer, Fall, Winter)

Spring, Summer Beaches
Fall Leaves
Winter Snowboarding
Winter, Spring Cold Weather

The results would be as follows (where the index function above (k$1
would change to l$1 if I dragged it across - the values in those cells
would be spring, summer, fall, winter)

Spring Summer Fall
Winter
Beaches Beaches Leaves
Snowboarding
Cold Weater
Cold Weather

Like I said, I can get the function to work if there is only one value
in a cell in column A, but I would like to have multiple values listed
so I don't need to make a bigger sheet.

Please help if you can. Thanks.

Index function using multiple values in one cell
 

Typo in the range:

SMALL(IF(ISNUMBER(SEARCH(K$1,$A$1:$A$100))

Should be:

SMALL(IF(ISNUMBER(SEARCH(K$1,$A$1:$A$1000))

Biff

"Biff" wrote in message
...
Hi!

Try changing this:

SMALL(IF($A$1:$A$1000=K$1

To:

SMALL(IF(ISNUMBER(SEARCH(K$1,$A$1:$A$100))

Biff

wrote in message
oups.com...
I have data that I have been categorizing into multiple categories.
What I would like to do is put all the of category types in one cell
(separated by a comma) that can be assigned to that specific data. I
have the Index function working right now that matches the value I have
and gives me the data that corresponds to that. See example below.

=INDEX($D$1:$D$1000,SMALL(IF($A$1:$A$1000=K$1,ROW( $A$1:$A$1000)-ROW(A$1)+1),ROWS($1:3)))

Data Example:

A B
C, D, E, F (Spring, Summer, Fall, Winter)

Spring, Summer Beaches
Fall Leaves
Winter Snowboarding
Winter, Spring Cold Weather

The results would be as follows (where the index function above (k$1
would change to l$1 if I dragged it across - the values in those cells
would be spring, summer, fall, winter)

Spring Summer Fall
Winter
Beaches Beaches Leaves
Snowboarding
Cold Weater
Cold Weather

Like I said, I can get the function to work if there is only one value
in a cell in column A, but I would like to have multiple values listed
so I don't need to make a bigger sheet.

Please help if you can. Thanks.