Find specific text in cell and do VLOOKUP with that text on separate table and return result

[fdirosa]

Here's a challenging question (looked everywhere on net for answer and none satifies my question).

I have bill of materials (hundreds of lines long) on worksheet #1 , which have part descriptions etc.. On worksheet #2 , I have two lists/tables: First labelled "ANYPOL" which contains specific words (full or partial), and second labelled "NOTPOL" which means key words (full or partial).


Ex. part description (which in worksheet #1 starting on column E11

WORKSHEET #1 "ANYPOL" TABLE

SOT223	FALSE
SOT22	TRUE

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

.

WORKSHEET #2 "NOTPOL" TABLE

BI-DI
CER

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

IC VOLT SOT22 REG POS ADJ TRUE
IC VOLT REG SOT22 BI-DI POS ADJ FALSE
IC VOLT REG POS SOT223 ADJ FALSE




Looking in part description for any specific text (ex.: package type which is "SOT-223-4") which is on worksheet #2 first table "ANYPOL" AND any text that is not on worksheet #2 second table "NOTPOL"

So formula would look something like this

IF(AND(ANY TEXT ON ANYPOL TABLE IN PART DESC?,ANY TEXT ON NOTPOL NOT IN PART DESC?),VLOOKUP TEXT THAT IS MATCHING IN PART DESC TO ANYPOL TABLE THEN RETURN VALUE FROM 2ND COLUMN IN ANYPOL TABLE ("TRUE OR FALSE","FALSE")

Now SOT22 is in description and also in ANYPOL but not in NOTPOL table so return result is "TRUE". Notice how I put ANYPOL table sorted from Z to A so it didn't match to SOT223 and give "FALSE" result?

IC VOLT SOT22 REG POS ADJ

Now SOT22 is in ANYPOL and BI-DI is in NOTPOL tables so logic will be false for AND statement and automatically return "FALSE" from VLOOKUP when logic fails.

IC VOLT REG SOT22 BI-DI POS ADJ

Finally, SOT22 is in description and also in ANYPOL but not in NOTPOL table so return result is "FALSE" from ANYPOL 2nd column.

IC VOLT REG POS SOT223 ADJ

So far this is the formula I have but not working properly:

=IF((AND((SUMPRODUCT(--ISNUMBER(SEARCH(ANYPOL,E11)))>0)=TRUE,SUMPRODUCT(--ISNUMBER(SEARCH(NOTPOL,E11)))>0)<>TRUE),VLOOKUP("*"&ANYPOL&"*",'DO NOT DELETE-MODIFY'!A2:B1963,2,0))

What is wrong with my formula please?

 

[oldbrewer]

		a123	apples
		b234	pears
		c789	plums
		a12
		b23
		app
		pea
		can you rephrase your question in terms of this simplistic layout please ?

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[fdirosa]

3 examples below

Descriptions. Results
123 abc 456_____ TRUE. (From table 1)
123 abc 789_____ FALSE. (Affected from table 2)
123 def ghi______ FALSE. (From table 1)


Vlookup table 1 (if description does not have words in table 2, bring back TRUE or FALSE in table 1)
Abc TRUE
Def FALSE

Vlookup table 2 (overrides table 1 if word is in table 2 list which is any where in description)
789
Jkl

Formula using ex. 1 above
If("abc" in desc is in table 1 AND not in table 2, do Vlookup for "abc" in table 1, return "TRUE" from table 1 column #2 )

 

[oldbrewer]

I do not understand post 1 at all that is why I asked you to use a simpler example initially

 

[fdirosa]

I do not understand post 1 at all that is why I asked you to use a simpler example initially

Very hard to explain or type up. I try again in a different way:



Find "word1" in a sentence (which is in cell A1) AND must not find "word2" in same sentence (cell A1).



word1 is in Label range called "GOODWORDS" (say C1:C5) and beside good words column are corresponding "TRUE" and "FALSE" column (D1:D5) will be used for VLOOKUP later.

word2 is in Label range called "BADWORDS" (say E1:E100) since I know a lot have of words that I won't mention them here ). That's it for bad words list



Let's say a 4-letter bad word was in sentence "A1". This means it automatically gives "FALSE" result (like Excel AND function) no matter how many good words are in same sentence.

Let's say a good word was found but no bad words in sentence "A1". Now, VLOOKUP allowed to find matching good word (C1:D5) in good words table and returns from 2nd column "TRUE".


So I hope this helps

 

[oldbrewer]

the door is green

4

the

door is green

5

door

5

is green

3

is

green

#######

#######

######

####

good words

bad words

my difficulty is how to break the sentence into words

door

1

pale

0

but once done every word can be tested against the good

car

1

light

0

list and the bad list

fence

1

dark

0

a found good word scores 1

a found bad word scores 0

multiply good score total by bad score total

a result of 0 proves a bad word was found

rules

no good words = "false"

the

0

the

1

door

1

door

1

one or more good words, no bad words = "true"

is

0

is

1

green

0

green

1

one of more good words, one or more bad words = "false"

sum

1

1

product

multiply sum by product

1

as sum >0 and produuct = 1 good words are present, no bad words are present

<colgroup><col span="3"><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[fdirosa]

the door is green

4

the

door is green

5

door

5

is green

3

is

green

#######

#######

######

####

good words

bad words

my difficulty is how to break the sentence into words

door

1

pale

0

but once done every word can be tested against the good

car

1

light

0

list and the bad list

fence

1

dark

0

a found good word scores 1

a found bad word scores 0

multiply good score total by bad score total

a result of 0 proves a bad word was found

rules

no good words = "false"

the

0

the

1

door

1

door

1

one or more good words, no bad words = "true"

is

0

is

1

green

0

green

1

one of more good words, one or more bad words = "false"

sum

1

1

product

multiply sum by product

1

as sum >0 and produuct = 1 good words are present, no bad words are present

<tbody>
</tbody>

OK thanks I think that will do.