Index Match problem

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? :ROFLMAO:
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

drallab57

New Member
Joined
Apr 29, 2014
Messages
13
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

Product_
KeyWord1Group NameKeyWord2 LessWord1
BuffaloBuffaloHuman Skull
Abstract BuffaloBuffalo AbstractGeometric
Bubalus Bubalis HornBuffalo Horn
Water BuffaloWater Buffalo Engraved

<tbody>
</tbody>


<tbody>
</tbody>
 

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
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
 

drallab57

New Member
Joined
Apr 29, 2014
Messages
13

ADVERTISEMENT

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?





 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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?


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.
 

drallab57

New Member
Joined
Apr 29, 2014
Messages
13

ADVERTISEMENT

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.

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

DESCRIPTION
Extra Large, Authentic Carved Brown Buffalo Skull - Bone, Traditional Sanskrit Mandala motif,

<tbody>
</tbody>
Category
Buffalo

<tbody>
</tbody>
Finely Engraved Large Horn of Water Buffalo - Bubalus Bubalis Horn - Dragon Phoenix Motif -

<tbody>
</tbody>
Buffalo Horn

<tbody>
</tbody>
Heavy Abstract Buffalo Statue of Bronze - Bronze -

<tbody>
</tbody>
Buffalo Abstract

<tbody>
</tbody>
Water Buffalo Skull - Original and Authentic - Bone, Matte Black - Bubalis Bubalus -

<tbody>
</tbody>
Water Buffalo Matt Black

<tbody>
</tbody>
Water buffalo skull with engraving of Mandala - Bubalus Bubalis - Bone -

<tbody>
</tbody>
Water Buffalo Engraved

<tbody>
</tbody>

<tbody>
</tbody>


A table where the Category is chosen from

CategoryKeyword1Keyword2Lessword1Lessword2
Buffalo

<tbody>
</tbody>
Buffalo

<tbody>
</tbody>
Water Buffalo

<tbody>
</tbody>
Abstract

<tbody>
</tbody>
Buffalo Abstract

<tbody>
</tbody>
Buffalo

<tbody>
</tbody>
Abstract

<tbody>
</tbody>
Water Buffalo

<tbody>
</tbody>
Buffalo Horn

<tbody>
</tbody>
Bubalus Bubalis Horn

<tbody>
</tbody>
Water Buffalo

<tbody>
</tbody>
Water Buffalo

<tbody>
</tbody>
Water Buffalo

<tbody>
</tbody>
Uncarved

<tbody>
</tbody>
Water Buffalo Engraved

<tbody>
</tbody>
Water Buffalo

<tbody>
</tbody>
Engraved

<tbody>
</tbody>
Water Buffalo Matt Black

<tbody>
</tbody>
Water Buffalo

<tbody>
</tbody>
Matte Black

<tbody>
</tbody>
Uncarved

<tbody>
</tbody>

<tbody>
</tbody>
 

drallab57

New Member
Joined
Apr 29, 2014
Messages
13
Marcello - about to answer point 3. I could not add text below bottom table so had to post or loose!
 

drallab57

New Member
Joined
Apr 29, 2014
Messages
13
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.

Water Buffalo
It contains Water Buffalo and Uncarved
BuffaloIt contains Buffalo but not Water Buffalo nor Abstract
Buffalo HornIt contains Bubalus Bubalis Horn
Buffalo AbstractIt contains Buffalo and Abstract but not Water Buffalo
Water Buffalo Matt BlackIt contains Water Buffalo and Matte Black but not uncarved
Water Buffalo EngravedIt contains Water Buffalo and Engraved

<colgroup><col><col></colgroup><tbody>
</tbody>

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!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
I don't understand the second example (in red)

DESCRIPTION
Extra Large, Authentic Carved Brown Buffalo Skull - Bone, Traditional Sanskrit Mandala motif,

<tbody>
</tbody>
Category
Buffalo

<tbody>
</tbody>
Finely Engraved Large Horn of Water Buffalo - Bubalus Bubalis Horn - Dragon Phoenix Motif -



<tbody>
</tbody>
Buffalo Horn

<tbody>
</tbody>

<tbody>
</tbody>

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

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top