INDEX similar names in different rows

M

mariekek5

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
 
J

Jacob Skaria

There are typos in your formula
<<=INDEX(A1:C9;MACTH("dog";B1:B9;0);MATCH("category";A1:C:1;0)

try
=INDEX(A1:C10,MATCH("Dog",B1:B10,0),MATCH("category",A1:C1,0))

If this post helps click Yes
 
M

mariekek5

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

Jacob Skaria

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
 
Top