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?
[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?