Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index Match function for multiple linked variables | Excel Worksheet Functions | |||
Index Match function for multiple linked variables | Excel Worksheet Functions | |||
Counting Multiple Values In A Cell | Excel Worksheet Functions | |||
cell color index comparison | New Users to Excel | |||
Toggle multiple values in single cell | Excel Worksheet Functions |