Results 1 to 6 of 6

Comparing two columns in excel using vlookup

This is a discussion on Comparing two columns in excel using vlookup within the Excel Questions forums, part of the Question Forums category; Hi, I want to compare two columns in excel and am using VLOOKUP for the same. The criteria for search ...

  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    3

    Default Comparing two columns in excel using vlookup

    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

  2. #2
    New Member
    Join Date
    Aug 2010
    Posts
    3

    Default Re: Comparing two columns in excel using vlookup

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

  3. #3
    New Member
    Join Date
    Jul 2009
    Posts
    37

    Default Re: Comparing two columns in excel using vlookup

    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)

  4. #4
    New Member
    Join Date
    Aug 2010
    Posts
    3

    Default Re: Comparing two columns in excel using vlookup

    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.

  5. #5
    Board Regular shemayisroel's Avatar
    Join Date
    Sep 2008
    Location
    Sydney - Australia
    Posts
    1,859

    Default Re: Comparing two columns in excel using vlookup

    Quote Originally Posted by AakritiR View Post
    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.
    enercheenhologoskaihoogosenprostontheonkaitheosenhologosnarchhnoogovkailogohnprvtonqeonkaiqeovhnologov

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,440

    Default Re: Comparing two columns in excel using vlookup

    Possibly also this:

    =IF(COUNTIF(B:B,LEFT(A2,4)&"*"),A2,"Not the same")
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com