Fuzzy logic VLOOKUP (I think)

[jimhegs]

I have a problem I just can find an answer to so I will bring it to the pros, I hope I can be have this make sense here it goes.

I have two sheets in a workbook, sheet1 has a list of company names (10,000+ rows) and other info, sheet2 has a list (750 rows) of "things" that I need to search for anywhere in my list of company names (they will not be exact matches) I would like to indicate in Sheet1 Column B (or in any other sortable manner) if there is any match from the list of terms on sheet2 with the data in sheet1 column A. I am not opposed to buying an add-in but I havn't been able to track one down that will do what I need.

Here is some sample data with the desired results listed at the bottom.

(Sheet1 Column A)
Bob's Roofing
Big Time Law Office
Evergreen School District
Eastside Hospital
Clunker Cars Direct
Snyder Roofing
Casa' Del Evergreen

(Sheet2 Column A)
Evergreen
Cars Direct
Snyder Roofing

(Desired Results) with the true false in a new column??
Bob's Roofing, FALSE
Big Time Law Office, FALSE
Evergreen School District, TRUE
Eastside Hospital, FALSE
Clunker Cars Direct, TRUE
Snyder Roofing, TRUE
Case Del Evergreen, TRUE

[ratcat]

G'day and welcome to the Forum,

Try this at Sheet 1 Cell B2 and copy drag down

Please Login or Register  to view this content.

Cheers

RC

[sailepaty]

Maybe this,

=SUMPRODUCT(ISNUMBER(FIND(Sheet2!$A$1:$A$3,A1))*1)>0

Regards

[jimhegs]

Thanks but both of those did not work (Argh..) This is driving me crazy, it can't be this hard but...

I will attach a sample file for anyone to play with

Customers are on the 1st sheet
Search Terms on Sheet 2
A sample of what I would like the results to look like are in the 3rd sheet. it doesn't need to be True/False but I would need to be able to sort the non matches out of the results.

[quekbc]

Got it.

At Sheet1!B1, type in...

Please Login or Register  to view this content.

Then press ctrl+shift+enter.

[jimhegs]

At Sheet1!B1, type in...

I can't get this @%^(# thing to work. Would it be possible for someone to get it working in the sample file I provided a few posts ago and then re-upload it so I can see it in action.

Thanks,
Jim

[rwgrietveld]

Put this is the curstomers' sheet in B1

[quekbc]

Here's the attached file.

Remember.. the ctrl+shift+enter is very important.

[sailepaty]

The SUMPRODUCT formula works, but the rwgrietveld formula with a samll adjustment will be 20% faster.

If you have 10000+ companies and 750 Keywords any formula could take +- 1 minute to get the results.


=ISNUMBER(LOOKUP(9E+99+307,FIND(KeyWords!$A$1:$A$7,A1)))


Regards

[rwgrietveld]

Doing a small test resulted in the fact that lookup in combination with Find is the fastest closely followed by the sumproduct and mine lookup (search) come is last by a factor 5 !!

tested with 20.000 companies

[jimhegs]

Thanks everyone, it worked great except for being case sensitive, but I was able to take care of that pretty easily.

Thanks again for your help

[simpo]

=ISNUMBER(LOOKUP(9E+99+307,FIND(KeyWords!$A$1:$A$7,A1)))

What does the 9E+99+307 mean/do?

[abousetta]

Largest number allowed to be typed into a cell is 9.99999999999999E+307 (See http://office.microsoft.com/en-us/ex...005199291.aspx for more limitations.

abousetta

[simpo]

Thanks, any ideas how you would use the above formula =ISNUMBER(LOOKUP(9E+99+307,FIND(KeyWords!$A$1:$A$7,A1))) but it also finds words that are of a close match. E.g. Matches 5 characters will still return true as I have two tables that I'm comparing but sometimes there are typos in one tables but they both mean the same thing.g

[simpo]

Think I worked it out with something like this: =MATCH("*"&LEFT(D2,5)&"*",$E$2:$E$6777,0)

[abousetta]

That looks like it should work. Let me know if it doesn't.

abousetta