Index Match problem

D

drallab57

New Member
Joined
Apr 29, 2014
Messages
13
Using INDEX MATCH I can check if a key word is in a cell and return the name of the group it belongs to. Fine. But what if I want additionally check to see that it does not contain a word/phrase that would mean it is in another group, or there is a second keyword which must be there for a particular Group?

The description of a product could be something like
Hand Carved Human Skull - Tribal Style Carving - Traditional Dayak and Aztec Carving - Dalbergia Latifolia wood - Buffalo Bone Teeth


In the list of key words/phases are

Buffalo

Human Skull.


This item belongs to the Human Skull group, not the Buffalo Group, but Buffalo is given as the result.



The cell contains this array formula:

=INDEX(Product_Category_<wbr>GroupName,MATCH(TRUE,ISNUMBER(<wbr>SEARCH(Prod_Cat_KeyWord1,$<wbr>L955)),0))



Product_Category_GroupName <wbr> is a list in another table

Prod_Cat_KeyWord1 <wbr> is another list in that table, for words to be found

Prod_Cat_LessWord1 <wbr> is another list in that table, for words that do not want to be in the product description



Also I might have additional words that might have to be in the product description for it to be selected.

Prod_Cat_KeyWord2



and further Lessword(s) that would stop that product being in a category in a Prod_Cat_LessWord2 column

Any ideas? :rofl:
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
KeyWord1Group NameKeyWord2 LessWord1
BuffaloHuman Skull
Buffalo AbstractGeometric
Buffalo Horn
Water Buffalo Engraved

<tbody>
[TD="class: xl65"]Hi Doug - Thank you for replying.
Your suggestion for filters is interesting but I feel that is not quite what I am after as I am trying to find a match.

I am trying to populate a cell with a Group Name list dependent on what is in the Description cell. I have been using INDEX MATCH for some time to do this checking out a list of possibilities in the Keyword list. However there are a number of times when I wish to be more selective by having another KeyWord, Keyword2 , found at the same time in the Description but that Keyword is only relevant when a particular KeyWord1 is being considered. And also there are times when a Group Name should not be chosen because there is, for that Group Name some word(s) that will discount it - LessWord1.

This formula works for one criterium
=INDEX(Product_Category_GroupName,MATCH(TRUE,ISNUMBER(SEARCH(Prod_Cat_KeyWord1,$L379)),0))

I wish to do it for 2 criteria matching, and also be able to 'discard' if it has something not wanted, something I have called a LessWord.

Any ideas?:confused:

Sample descriptions
25. Extra Large, Authentic Carved Brown Buffalo Skull
26. Finely Engraved large horn of a water buffalo (Bubalus bubalis horn)

and these would result in the following groups
Buffalo (#25)
Buffalo Horn (#26)


This is a sample part of the table

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="width: 117"]Product_[/TD]
[TD="width: 154"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 88"][/TD]

[TD="width: 117"]Buffalo[/TD]

[TD="width: 117"]Abstract Buffalo[/TD]

[TD="width: 117"]Bubalus Bubalis Horn[/TD]

[TD="width: 117"]Water Buffalo[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
So I had never seen a formula setup like yours with the MATCH(TRUE,ISNUMBER....) but I found it here...

https://exceljet.net/formula/get-first-match-cell-contains

I like it...I think that will come in handy sometime.

It led me to this... https://exceljet.net/formula/cell-contains-one-of-many-things

On that page, there is a link to this, https://exceljet.net/formula/cell-contains-one-of-many-with-exclusions ; which I think is what you are looking to do with "includes" and "excludes"...

Hope that helps,

Doug
 
Upvote 0
Hi Doug and anyone else who comes across this.

I am still having a problem.
Doug - helpful and interesting as you reply was I was unable to progress. (I had already visited these pages earlier in my search to solve this problem)

I restate what I am after in what may be a clearer manner:

I have products with descriptions. From the description I wish to categorise them. On this simple level I can do this by this formula

=IFERROR(INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(KeyWord1,$L22)),0)),"Miscellaneous")


Using CTL+Shift+Enter

(Miscellaneous is the default if none of the keywords are found to be in the Description)

I am unable to get this to work when there is more than one search element.

I have a reference table that has a Five columns


  1. A list of Keywords, named Keyword1, that are being searched
  2. A list of Categories that will apply to the keyword if found and it particular to that Keyword
  3. A list of Lesswords (which are particular to the keyword in whose line it is), named Lessword1, which if found will stop that Keyword on the row being found (selected)
  4. A list Keyword2, which as well as Keyword1 has to be found
  5. A list Lessword2, which will have the same effect as Lessword1 if found,
  6. An empty cell in Keyword2, Lessword1 and Lessword2 means they have no effect on Keyword1

 Can anyone help please.
Is there a way of refining the search so that Keyword2 has to be found for that Category to be selected?
Is there a way of further refining the search so that if a lessword is applicable to a category then that category will not be selected?





 
Upvote 0
drallab57 said:
I am unable to get this to work when there is more than one search element.

I have a reference table that has a Five columns


  1. A list of Keywords, named Keyword1, that are being searched
  2. A list of Categories that will apply to the keyword if found and it particular to that Keyword
  3. A list of Lesswords (which are particular to the keyword in whose line it is), named Lessword1, which if found will stop that Keyword on the row being found (selected)
  4. A list Keyword2, which as well as Keyword1 has to be found
  5. A list Lessword2, which will have the same effect as Lessword1 if found,
  6. An empty cell in Keyword2, Lessword1 and Lessword2 means they have no effect on Keyword1

Can anyone help please.
Is there a way of refining the search so that Keyword2 has to be found for that Category to be selected?
Is there a way of further refining the search so that if a lessword is applicable to a category then that category will not be selected?
Click to expand...

To try to help you, we need:
1. Criteria when more than one keyword is found;
2. A small sample containing the five columns of the table (keywords1, category, lessword1, keywords2, lessword2)
3. Examples along with the desired results and the logic to obtain them.

M.
 
Upvote 0
Marcelo Branco said:
To try to help you, we need:
1. Criteria when more than one keyword is found;
2. A small sample containing the five columns of the table (keywords1, category, lessword1, keywords2, lessword2)
3. Examples along with the desired results and the logic to obtain them.

M.
Click to expand...

Marcelo - thanks for the swift response!

1. All the Keyword1 are generally different but on some occasions they could repeat but would have either a Keyword2 or a Lessword1 to differentiate it. I have been using the index function to select the Group Name / Category [I used the former title in the 3rd post and the latter in the 4th post ago but they are the same] based upon a Keyword1 being found in the Description. I have been doing it with 20+ helper columns - not a great way of doing things.

2. Sample Table showing Description and desired Categtory

[TABLE="width: 700"]
<tbody>[TR]
[TD]
DESCRIPTION
Extra Large, Authentic Carved Brown Buffalo Skull - Bone, Traditional Sanskrit Mandala motif,

<tbody>
</tbody>
[/TD]
[TD][TABLE="width: 163"]
<tbody>[TR]
[TD="width: 163"]Category[/TD]
[/TR]
[TR]
[TD="width: 163"]Buffalo[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 587"]
<tbody>[TR]
[TD]Finely Engraved Large Horn of Water Buffalo - Bubalus Bubalis Horn - Dragon Phoenix Motif -[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
Buffalo Horn

<tbody>
</tbody>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 587"]
<tbody>[TR]
[TD="width: 587"]Heavy Abstract Buffalo Statue of Bronze - Bronze -[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
Buffalo Abstract

<tbody>
</tbody>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 587"]
<tbody>[TR]
[TD]Water Buffalo Skull - Original and Authentic - Bone, Matte Black - Bubalis Bubalus -[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
Water Buffalo Matt Black

<tbody>
</tbody>
[/TD]
[/TR]
[TR]
[TD]
Water buffalo skull with engraving of Mandala - Bubalus Bubalis - Bone -

<tbody>
</tbody>
[/TD]
[TD]
Water Buffalo Engraved

<tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]


A table where the Category is chosen from

[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Keyword1[/TD]
[TD]Keyword2[/TD]
[TD]Lessword1[/TD]
[TD]Lessword2[/TD]
[/TR]
[TR]
[TD]
Buffalo

<tbody>
</tbody>
[/TD]
[TD]
Buffalo

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]Water Buffalo[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
Abstract

<tbody>
</tbody>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 163"]
<tbody>[TR]
[TD="width: 163"]Buffalo Abstract[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
Buffalo

<tbody>
</tbody>
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]Abstract[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
Water Buffalo

<tbody>
</tbody>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 163"]
<tbody>[TR]
[TD="class: xl65, width: 163"]Buffalo Horn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
Bubalus Bubalis Horn

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD]
Water Buffalo

<tbody>
</tbody>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Water Buffalo

<tbody>
</tbody>
[/TD]
[TD]
Water Buffalo

<tbody>
</tbody>
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]Uncarved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Water Buffalo Engraved

<tbody>
</tbody>
[/TD]
[TD]
Water Buffalo

<tbody>
</tbody>
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]Engraved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Water Buffalo Matt Black

<tbody>
</tbody>
[/TD]
[TD]
Water Buffalo

<tbody>
</tbody>
[/TD]
[TD]
Matte Black

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD]
Uncarved

<tbody>
[TD="width: 94"][/TD]

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
The logic I hope to use is that when a Match is found on the Primary Word (Keyword1) then the other cells in that row of the table are taken into account.

Once I can see how to do it for this then I can replicate it. Maybe even go to Keyword3 or Lessword3.

[TABLE="class: grid, width: 750"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Water Buffalo
[/TD]
[TD]It contains Water Buffalo and Uncarved[/TD]
[/TR]
[TR]
[TD]Buffalo[/TD]
[TD]It contains Buffalo but not Water Buffalo nor Abstract[/TD]
[/TR]
[TR]
[TD]Buffalo Horn[/TD]
[TD]It contains Bubalus Bubalis Horn[/TD]
[/TR]
[TR]
[TD]Buffalo Abstract[/TD]
[TD]It contains Buffalo and Abstract but not Water Buffalo
[/TD]
[/TR]
[TR]
[TD]Water Buffalo Matt Black[/TD]
[TD]It contains Water Buffalo and Matte Black but not uncarved[/TD]
[/TR]
[TR]
[TD]Water Buffalo Engraved[/TD]
[TD]It contains Water Buffalo and Engraved[/TD]
[/TR]
</tbody>[/TABLE]

Or have I misunderstood the capabilities of Excel and should be looking at a database such as Access? I hope not as Excel is an old friend and Access is not!

Thanks for your time and hope you can help.

Cymru Am Byth!
 
Upvote 0
I don't understand the second example (in red)

[TABLE="class: cms_table, width: 700"]
<tbody>[TR]
[TD][TABLE="class: cms_table"]
<tbody>[TR]
[TD]DESCRIPTION[/TD]
[/TR]
[TR]
[TD]Extra Large, Authentic Carved Brown Buffalo Skull - Bone, Traditional Sanskrit Mandala motif,[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 163"]
<tbody>[TR]
[TD="width: 163"]Category[/TD]
[/TR]
[TR]
[TD="width: 163"]Buffalo[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 587"]
<tbody>[TR]
[TD]Finely Engraved Large Horn of Water Buffalo - Bubalus Bubalis Horn - Dragon Phoenix Motif -


[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table"]
<tbody>[TR]
[TD]Buffalo Horn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Itcontains Bubalus Bubalis Horn (3rd keyword1) but also contains Water Buffalo (Lessword1 in the same row)

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Index match function
Replies
3
Views
746
Alex Blakenburg
A
C
  • Question Question
Comparing 3 tables to a list of numbers, if match, return 4 columns worth of data on another tab
Replies
4
Views
366
alansidman
alansidman
N
  • Solved
Problem populating table based off two criteria from a different table
Replies
1
Views
875
Nade85
N
C
  • Question Question
Remove spaces from index/match list
Replies
3
Views
2K
Marcelo Branco
M
D
  • Question Question
Return lookup/index(match) list based on partial text - with no blanks
Replies
5
Views
951
Fluff
Fluff

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top