search for exact phrases in column of text strings

[futurejock]

Hi,

I may need just some nomenclature or switch

I have named a range of "find text" in a column.
I am searcing a "within text" column of cells, each with a text string.
I want to return match / no match for exact phrases, in exact order, NOT case sensitive.
I also want to continue getting a match for sincle words in the "find text" column (list).

Currently I get a match if a complete find text word is found, which is good since I don't want partial words or compound words in the text strings to match.

BUT if there are two or more words in my find text phrase,
I get a match even if one word is in the "within text" column.

For instance:

find text:
brown cow
yellow cow

within text:
the cow jumped over the moon
2) the brown cow is a funny cow
the cow is mellow, the one that is yellow
brown and yellow are the names of my cows
5) ****brown paddies line the cowyard
my son's hair has a brown cowlick.

I ONLY want to "match" for string example #2,
AND I want "No Match" for all the others.

Currently all of these except #5 match.

Also I have two ranges searching right now such as

CowsILike
Brown Cow
Yellow Cow

CowsIDon'tLike
Red Cow
Blue Cow

and the formula I am using is this:

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&CowsILike&" "," "&A4&" "),CowsILike)),IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&CowsIDon'tLike&" "," "&A4&" "),CowsIDon'tLike)),"MAYBECowsI'llConsiderLiking","CowsIDOn'tLike"),"CowsILike")


Thanks,

Dan

 

[jasonb75]

Re-check your "real" formula Dan, I couldn't see any reason why your sample wouldn't work, copiedit to a sheet to check and got your desired result, like for #2, consider for the rest.

I'm not able to reproduce the incorrect results you said you get, so can't see what the error could be.

 

[futurejock]

Hi thanks,

I will check again, but I think maybe my question got lost in the description I gave.

To answer you I will try it again / rebuild to test. Maybe it's confusing which result comes from which IF statement. Can you re-reun your test of my formula and change the result from the second IF from "CowsILIke" option to "CowsIOwn"?

Then, the problem I am struggling with is not which argument gives the a return, but rather that either argument give a YES/Match for any of those statements other than #2.

#2 is the only exact phrase "brown cow," meaning it has both words in the correct order.

Question: How to I ask the search to search for ONLY exact phrases in the exact order?

e.g. in a boolean search I would put quotes around

Thanks for your effort,

Dan

PS maybe we should re-write it with new strategy for my uber problem?
Which is simply how do I write 2 to 4 columns of "find text" arrays, which include phrases and singular words, which must be preserved or found exactly (definition= all letters of all words in correct order and all wordes in correct order, and disrespecting any case sensitivity (in either direction)), in a column of text strings?

 

[jasonb75]

As far as I can see this is doing what you need, I've removed the lookups as they serve no purpose in this situation, that is only testing for a number returned from the search function, ISNUMBER() is a far more efficient way of doing this.

Excel Workbook
	A	B	C	D	E
4	the cow jumped over the moon	No Match		list 1	list 2
5	the brown cow is a funny cow	List 1		Brown Cow	Red Cow
6	the cow is mellow, the one that is yellow	No Match		Yellow Cow	Blue Cow
7	brown and yellow are the names of my cows	No Match
8	dirtbrown paddies line the cowyard	No Match
9	my son's hair has a brown cowlick.	No Match
Sheet

 

[futurejock]

Hi Jason,

This did nt work for me.

Maybe because I am in 2003?

Or maybe because I have my lists defined as dynamic. Maybe that's why I need LOOKUP(9.99999999999999E+307 ??

Anyway simplifying it as you showed is good, cause I see better how I can keep nesting rules to get a greater sort (less margin of error) if I keep adding lists (as additional variables).

D

 

[jasonb75]

What formula are you using to define the dynamic ranges?

Not something I make a habit of using but I recall problems with them before, in theory if I use the same method as you have to define the ranges I should get the same errors.

 

[futurejock]

HI Jason,

thanks for your help. It's jts an idea the dynamic ranges. I don't thinkt it's the only variable. Something really weird is happening that the if true/if false locations seem to swap.

I'm not going to be able to mess with this timesaver anymore. and now have to work manually on my deadline.

anyway again, thanks.

D

 

[hiker95]

jasonb75,

Nicely done - I can use this - thanks.

 

[jasonb75]

Just found it doesn't work properly, hence the reason why Aladin advised using lookup bignum http://www.mrexcel.com/forum/showthread.php?t=477848

Notice that where the match was found in list1, both the phrase in column A and the expression in list 1 are both in row 5, if something is moved so that they are on different rows of the worksheet then "No match"!

My error, the formula provided the desired result on the sample so I didn't test thoroughly.