Partial String Match

[tcarterg]

I've got two lists that I want to compare and return the value in A column. I've tried the following to no avail.

List #1	List #2	Results
Wells Fargo Bank, NA	Wells Fargo Bank Sioux Falls, S.D
Citigroup Global Markets, INC	City National Bank Los Angeles
JPMorgan Securities, INC.	JPMorgan Chase Bank Columbus, Ohio
JPMorgan Chase & Co	Bank of America, USA

<tbody>
</tbody>

=LOOKUP(1E+100,SEARCH(A2:A5,B2),A2:A5) - no results

and

=VLOOKUP(CONCATENATE("*",LEFT(B2,8),"*"),A2:A5,1,FALSE)

too broad, comes up with unrelated results, even when expanding it out from 8 to 12-15.

Any help?

 

[Aladin Akyurek]

What are the results that you expect?

 

[tcarterg]

I'd like to see it come back with the matching result in column A. So the above would look like this:

List #1	List #2	Results
Wells Fargo Bank, NA	Wells Fargo Bank Sioux Falls, S.D	Wells Fargo Bank, NA
Citigroup Global Markets, INC	City National Bank Los Angeles	N/A
JPMorgan Securities, INC.	JPMorgan Chase Bank Columbus, Ohio	JPMorgan Chase & Co
JPMorgan Chase & Co	Bank of America, USA	N/A

<tbody>
</tbody>

 

[Aladin Akyurek]

I'd like to see it come back with the matching result in column A. So the above would look like this:

List #1	List #2	Results
Wells Fargo Bank, NA	Wells Fargo Bank Sioux Falls, S.D	Wells Fargo Bank, NA
Citigroup Global Markets, INC	City National Bank Los Angeles	N/A
JPMorgan Securities, INC.	JPMorgan Chase Bank Columbus, Ohio	JPMorgan Chase & Co
JPMorgan Chase & Co	Bank of America, USA	N/A

<TBODY>
</TBODY>

This cannot be done with the LOOKUP formula even if you use the most convenient constant as look up value.

Have a look rather at:
http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

 

[tcarterg]

I've looked at it but I haven't tried it. Seeing as you are pointing me towards it and I've seen it twice now in related searches on here, It might be the way to go, but I wanted to see if I was just going the long way around the barn and someone knew a function I could use.