Compare first 4 characters question

[goodiein2808]

First of all, thank you for your precious time.

Second, this method must be applicable to not only english, but to korean, chinese as well.

So here is what I want: there is a column A full of characters, I want to look each grid of them up in another column from another excel file — by comparing the first 4 characters, starting from the left.

If the first 4 char is same, no matter how different the rest of them are different, I want a value returned such as "yes", if the first 4 isn't the same, no matter how exactly the rest are alike, want a value returned as "no".

Example:

look up Bohemian in: "Barack" "Hamas" "Bohener", the return value should be "yes".

Your help would be greatly appreciated, thanks in advance, and BTW, if I did not make the issue clear, please reply and let me know.

[goodiein2808]

Oh and, if there is less than 4 char, the yes shall be returned only all 1/2/3 char matches.

[Azam Ali]

the attatch file may be helpful

[zbor]

Here, try this in B2 (according to AA example):

=IF(A2="","", IF(COUNTIF($D$2:$I$28, LEFT(A2,4)&"*")>0,"yes","no"))

[goodiein2808]

the attatch file may be helpful

Thanks Azam, your wonderful!

Checking it out right now... getting back with feedback in some time...

[goodiein2808]

Hat off once again to Azam, you saved my life LOL. Thanks, and thanks to other folks for your kind help!!!

[Azam Ali]

welcome

If your problem is solved, mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.