Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Index/Match function or similar exoticdisease Excel Worksheet Functions 5 March 13th 09 01:44 PM
similar names - two spreadsheets jtkuehn Excel Worksheet Functions 3 August 28th 08 10:07 PM
index/match or vlookup or similar [email protected] Excel Worksheet Functions 1 January 9th 08 09:16 PM
Adding blank rows between groups of similar names Justin Excel Worksheet Functions 2 July 6th 07 07:28 PM
SIMILAR NAMES IN A TABLE Dimitri Kryukov Excel Discussion (Misc queries) 5 June 28th 05 03:27 PM


All times are GMT +1. The time now is 02:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"