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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]List #1[/TD]
[TD]List #2[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]Wells Fargo Bank, NA[/TD]
[TD]Wells Fargo Bank Sioux Falls, S.D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Citigroup Global Markets, INC[/TD]
[TD]City National Bank Los Angeles[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JPMorgan Securities, INC.[/TD]
[TD]JPMorgan Chase Bank Columbus, Ohio[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co[/TD]
[TD]Bank of America, USA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


=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:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]List #1[/TD]
[TD]List #2[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]Wells Fargo Bank, NA[/TD]
[TD]Wells Fargo Bank Sioux Falls, S.D[/TD]
[TD]Wells Fargo Bank, NA[/TD]
[/TR]
[TR]
[TD]Citigroup Global Markets, INC[/TD]
[TD]City National Bank Los Angeles[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]JPMorgan Securities, INC.[/TD]
[TD]JPMorgan Chase Bank Columbus, Ohio[/TD]
[TD]JPMorgan Chase & Co[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co[/TD]
[TD]Bank of America, USA[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

I'd like to see it come back with the matching result in column A. So the above would look like this:
[TABLE="class: cms_table, width: 500"]
<TBODY>[TR]
[TD]List #1
[/TD]
[TD]List #2
[/TD]
[TD]Results
[/TD]
[/TR]
[TR]
[TD]Wells Fargo Bank, NA
[/TD]
[TD]Wells Fargo Bank Sioux Falls, S.D
[/TD]
[TD]Wells Fargo Bank, NA
[/TD]
[/TR]
[TR]
[TD]Citigroup Global Markets, INC
[/TD]
[TD]City National Bank Los Angeles
[/TD]
[TD]N/A
[/TD]
[/TR]
[TR]
[TD]JPMorgan Securities, INC.
[/TD]
[TD]JPMorgan Chase Bank Columbus, Ohio
[/TD]
[TD]JPMorgan Chase & Co
[/TD]
[/TR]
[TR]
[TD]JPMorgan Chase & Co
[/TD]
[TD]Bank of America, USA
[/TD]
[TD]N/A
[/TD]
[/TR]
</TBODY>[/TABLE]

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.