Comparing two columns in excel using vlookup

[AakritiR]

Hi,

I want to compare two columns in excel and am using VLOOKUP for the same.

The criteria for search is:

Suppose column A and column B are to be compared.Column C is the resultant column.

1) I need to restrict my search till 4 characters in column B, as there are entries in the column A with only last few characters being different.
For ex:

Column A Column B
-------- --------
Confuse Confuse
Confusion Tradition


The syntax I am using is:
In column C (resultant column)
=VLOOKUP(LEFT(A2,5)&"*",B:B,1,FALSE) and then drag this for the entire column entry.

(But issue with this is that post comparing with column B and it displays what's there in column B as below:

Column A Column B Column C
-------- -------- --------
Confuse Confuse Confuse
Confusion Tradition Confuse

2) I want the comparision result to be picked from column A, so that I can get all kinds of similar entries from column A into the resultant column.

For ex:

Column A Column B Column C
-------- -------- --------
Confuse Confuse Confuse
Confusion Tradition Confusion



Could someone please help me in getting the above output??

Thanx in Advance
Regards,
Aakriti

 

[AakritiR]

Could someone please help?????????????

 

[Archijs]

I am not sure i completely understood what you wanted to get but i think that this might help

=IF(ISNA(VLOOKUP(LEFT(A2,4)&"*",B:B,1,FALSE)),"Not the same",A2)

 

[AakritiR]

Thank you so much Archijs. This is what i wanted.
Just wondering how come this didnt click my head. Silly me.

Anyway, thanks in tons.

 

[shemayisroel]

Thank you so much Archijs. This is what i wanted.
Just wondering how come this didnt click my head. Silly me.

Anyway, thanks in tons.

I wouldn't worry about it. I see it time & time again and I've also been guilty of it several times where we over analyse the situation and come at it with a sledge hammer with a simple fly squatter would be suffice.

 

[Peter_SSs]

Possibly also this:

=IF(COUNTIF(B:B,LEFT(A2,4)&"*"),A2,"Not the same")