Index Match problem

[drallab57]

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?

 

[drallab57]

Marcelo

Sorry, you are quite correct to point out this inconsistency. I was acting quickly as you had been kind enough to answer promptly. I had hoped my sample data was right, but not so!

I am trying develop the spreadsheet to make it more user friendly and when I can see how the formulas work to select a category based on the principle below I hope to develop it.

There are 2 tables.

1. Data - 1000s of rows
2. Reference Table - 30+ rows

A Category will be selected and the result, of a formula, is in the Data table if:

1. A key word, Keyword1, is in the Description column in the Data table.

And additionally

1. Keyword2, that is associated with Keyword1, if there is one, being in the Description
2. There not being a Lessword1 in the Description, if there is a Lessword1 associated with that Keyword1
3. There not being a Lessword2 in the Description, if there is a Lessword2 associated with that Keyword1

The Reference Table has the following columns:
| Category | Keyword1 | Keyword2 | Lessword1 | Lessword2 |

I have achieved #1 using this formula. (Column L is where the Descriptions are in the Data table are.)

{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(KeyWord1,$L45)),0))}

I cannot see how to develop it further.

 

[drallab57]

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.

I was wrong to put Water Buffalo as a Lessword as Bubalus Bubalis Horn is unique in the descriptions! In error I copied it down as I was making up that sample table.

Tony

 

[Marcelo Branco]

I have achieved #1 using this formula. (Column L is where the Descriptions are in the Data table are.)

{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(KeyWord1,$L45)),0))}

I cannot see how to develop it further.

Let's try in stages.

First stage: considering only Keyword1 and Lessword1

Try something like this (not tested, but i think it should work)
=INDEX(Category,MATCH(1,IF(ISNUMBER(SEARCH(Keyword1,$L45)),IF(ISERROR(SEARCH(Lessword1,$L45)),1)),0))
confirmed with Ctrl+Shift+Enter

If it works try developing the formula further to include Keyword2 and Lessword2

By the way, try to provide more examples along with expected result.

M.