| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX similar names in different rows
Hi,
If I want to look for the right category belonging to a specific thing...and the table is as follows...that each specific thing has the same category....but there are more than one of them, because they have different numbers... Number Thing Category Z24 Dog Animal Z33 Dog Animal Z67 Dog Animal Z87 Dog Animal Z98 Baloon Toy Z99 Baloon Toy Z56 Baloon Toy Z78 Spoon Homearticle Z54 Spoon Homearticle Etc.... How can I find that the category of 'dog' is 'animal'? I use the index formula: =INDEX(A1:C9;MACTH("dog";B1:B9;0);MATCH("category" ;A1:C:1;0) BUt unfortunately this does not work, because there are more than 1 dogs.. Hope someone can help. Thanks a lot in advance. Marieke |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
INDEX similar names in different rows
Try
=INDEX(C1:C100,MATCH("Dog",B1:B100,0)) If this post helps click Yes --------------- Jacob Skaria "mariekek5" wrote: Hi, If I want to look for the right category belonging to a specific thing...and the table is as follows...that each specific thing has the same category....but there are more than one of them, because they have different numbers... Number Thing Category Z24 Dog Animal Z33 Dog Animal Z67 Dog Animal Z87 Dog Animal Z98 Baloon Toy Z99 Baloon Toy Z56 Baloon Toy Z78 Spoon Homearticle Z54 Spoon Homearticle Etc.... How can I find that the category of 'dog' is 'animal'? I use the index formula: =INDEX(A1:C9;MACTH("dog";B1:B9;0);MATCH("category" ;A1:C:1;0) BUt unfortunately this does not work, because there are more than 1 dogs.. Hope someone can help. Thanks a lot in advance. Marieke |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
INDEX similar names in different rows
There are typos in your formula
<<=INDEX(A1:C9;MACTH("dog";B1:B9;0);MATCH("categor y";A1:C:1;0) try =INDEX(A1:C10,MATCH("Dog",B1:B10,0),MATCH("categor y",A1:C1,0)) If this post helps click Yes --------------- Jacob Skaria "mariekek5" wrote: Hi, If I want to look for the right category belonging to a specific thing...and the table is as follows...that each specific thing has the same category....but there are more than one of them, because they have different numbers... Number Thing Category Z24 Dog Animal Z33 Dog Animal Z67 Dog Animal Z87 Dog Animal Z98 Baloon Toy Z99 Baloon Toy Z56 Baloon Toy Z78 Spoon Homearticle Z54 Spoon Homearticle Etc.... How can I find that the category of 'dog' is 'animal'? I use the index formula: =INDEX(A1:C9;MACTH("dog";B1:B9;0);MATCH("category" ;A1:C:1;0) BUt unfortunately this does not work, because there are more than 1 dogs.. Hope someone can help. Thanks a lot in advance. Marieke |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
INDEX similar names in different rows
HI Jacob,
This is the formula I actually use: =INDEX(A1:C9;MACTH("dog";B1:B9;0);MATCH("category" ;A1:C:1;0) It works when there is only 1 Dog, but not when there are more Dogs... That is my problem.. So I guess I have to adapt the formula.. "Jacob Skaria" wrote: There are typos in your formula <<=INDEX(A1:C9;MACTH("dog";B1:B9;0);MATCH("categor y";A1:C:1;0) try =INDEX(A1:C10,MATCH("Dog",B1:B10,0),MATCH("categor y",A1:C1,0)) If this post helps click Yes --------------- Jacob Skaria "mariekek5" wrote: Hi, If I want to look for the right category belonging to a specific thing...and the table is as follows...that each specific thing has the same category....but there are more than one of them, because they have different numbers... Number Thing Category Z24 Dog Animal Z33 Dog Animal Z67 Dog Animal Z87 Dog Animal Z98 Baloon Toy Z99 Baloon Toy Z56 Baloon Toy Z78 Spoon Homearticle Z54 Spoon Homearticle Etc.... How can I find that the category of 'dog' is 'animal'? I use the index formula: =INDEX(A1:C9;MACTH("dog";B1:B9;0);MATCH("category" ;A1:C:1;0) BUt unfortunately this does not work, because there are more than 1 dogs.. Hope someone can help. Thanks a lot in advance. Marieke |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
INDEX similar names in different rows
Try the below with multiple entries
=INDEX(A1:C9,MATCH("Dog",B1:B9,0),MATCH("category" ,A1:C1,0)) If this post helps click Yes --------------- Jacob Skaria "mariekek5" wrote: Community Message Not Available |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with Index/Match function or similar | Excel Worksheet Functions | |||
| similar names - two spreadsheets | Excel Worksheet Functions | |||
| index/match or vlookup or similar | Excel Worksheet Functions | |||
| Adding blank rows between groups of similar names | Excel Worksheet Functions | |||
| SIMILAR NAMES IN A TABLE | Excel Discussion (Misc queries) | |||
Hybrid Mode
