help - find each string of a cell within list of values

[markkos11]

Hi guys,


I have this problem, haven't figured out how to solve it with my basic excel skills, hope you can help me.


I have 2 lists of data. The first list might contain values with name, last name and/or team code and the second list contains full names and in some cases team name. Basically I need to know if every single word from the values of list1 appears in any of the values of list2 (the order doesn't matter as long as each word exists). For example:


The list1 might contain:
mike trout laa
det cabrera
kershaw clayton
col gonzalez carlos
davis bal
harper
mccutchen


The list2 contains:
mike trout
miguel cabrera
mccutchen
clayton kershaw
carlos gonzalez col
bal chris davis
bryce harper




So after applying the "function" next to each value of list1, the results would be the following:

list1 check
mike trout laa found
det cabrera notfound
kershaw clayton found
col gonzalez carlos found
davis bal notfound
harper notfound
mccutchen found


Your help is much appreciated!

 

[Aladin Akyurek]

Try...


[List1] Sheet1, A2...

B2, just enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$8,A2)),"found","not found")

 

[markkos11]

Thanks Aladin, I tried =IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$8,A2))),TRUE, FALSE) but it doesnt work, since is not recognizing the word if they are not in the same order. They need to be compared separately.

These are the results:
[TABLE="width: 199"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]list1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mike trout laa[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]det cabrera[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]kershaw clayton[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]col gonzalez carlos[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]davis bal[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]harper[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]mccutchen[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Thanks Aladin, I tried =IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$8,A2))),TRUE, FALSE) but it doesnt work, since is not recognizing the word if they are not in the same order. They need to be compared separately.

These are the results:
[TABLE="width: 199"]
<tbody>[TR]
[TD]list1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mike trout laa[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]det cabrera[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]kershaw clayton[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]col gonzalez carlos[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]davis bal[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]harper[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]mccutchen[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

If you want to match a set of words, disregarding the word order, try rather:

http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

 

[markkos11]

I'll have a look and post something back, thanks!

 

[markkos11]

I've been trying to figure out the "fuzzyvlookup" function to solve my problem but I haven't achieved yet. In a nuttshell, the comparison seems not to work for these cases:

list1:
mike trout ana
kershaw clayton

list2:
mike trout
clayton kershaw

I'm using the function as =FuzzyVLookup(A2,Sheet2!$A:$A,1,80%,,2), still the function is finding a match for "mike trout ana" where there's actually not an exact match (I don't understand here why the fuzzypercentage returns 100% when the content of the cells are not the exactly the same. And for the second entry somehow is not recognizing is the same name but in different order. I hope I'm using the function incorrectly and that there's a solution!

The expected result should be:
mike trout ana #N/A
kershaw clayton clayton kershaw