Closest Match Formula

[bflan0524]

hello i am trying to formula to identify if a value is in both columns, the entries are in text format for example below
so in the below i would have a column C where i did a lookup of the value in column B compared to column A and it would return the value in column A, does that make sense? right now if i did a normal lookup it would come back #NA because column A does not match column B exactly

Column A	Column B
John Doe Inc	John Doe

 

[bflan0524]

Arrgh, I didn't see that the case doesn't match and there will be blank cells. Please test with below tweak.
=LOOKUP(2^15,SEARCH(A:A,B3,1)/LEN(A:A),A:A)
Note: These formulas will be quite taxing on the worksheet. See if you can limit to specific ranges on your sheet!

thanks again, so you can stop any time you like but now it worked for the example above but now column A=DEPENDABLE CLEANERS INC and column B=DEPENDABLE CLEANERS but it doesnt return a match.

 

[bflan0524]

Arrgh, I didn't see that the case doesn't match and there will be blank cells. Please test with below tweak.
=LOOKUP(2^15,SEARCH(A:A,B3,1)/LEN(A:A),A:A)
Note: These formulas will be quite taxing on the worksheet. See if you can limit to specific ranges on your sheet!

feel free to quit anytime, and that did work but still has issues. for example column A=DEPENDABLE CLEANERS INC where column B=DEPENDABLE CLEANERS but column C didnt return anything

ty again for all your help

 

[bflan0524]

whoops didnt see my previous one posted, sorry

 

[taurean]

whoops didnt see my previous one posted, sorry

With your data which is all mixed up I am getting feeling that you should be better off using below construct:
=IFERROR(LOOKUP(2^15,SEARCH(B1,A:A,1),A:A),LOOKUP(2^15,SEARCH(A:A,B1,1)/LEN(A:A),A:A))

 

[Kamolga]

=Index(A:B;MATCH("*"&B1&"*";A:A;0);1) should work whatever the sorting, blank cells and capital/small letters. You can just put a Iferror or IfNA for the case it does not exist.