Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Using Vlookup to compare 2 columns of data

This is a discussion on Using Vlookup to compare 2 columns of data within the Excel Questions forums, part of the Question Forums category; Hi everyone, I'm having difficulties using vlookup to compare two columns of TEXT data. My goal is find out how ...

  1. #1
    New Member
    Join Date
    Nov 2011
    Posts
    8

    Default Using Vlookup to compare 2 columns of data

    Hi everyone,

    I'm having difficulties using vlookup to compare two columns of TEXT data. My goal is find out how many of items in the "Eligible Serial Numbers" list have been ordered for destruction. The items listed for destruction are listed in the "Serial Numbers (destroyed).

    Whenever I put in a VLOOKUP, I get a serial number that doesn't make sense to me. For example, if I want to find out if "362351581" from the Eligible List appears on the destroyed list. What do you suggest?

    Code:
    Destroyed? (i.e. Vlookup formula in this column) Eligible Serial Numbers Serial Numbers (destroyed)
    362351581 362351581
    362351582 362351582
    362351584 362351584
    362351585 362351585
    362351590 362351590
    362351603 362351603
    362351604 362351604
    362351605 362351605
    362351611 362351611
    362351612 362351612
    362351617 99379006
    362351618 99379007
    362351619 142353015
    362351620 142353016

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    7,798

    Default Re: Using Vlookup to compare 2 columns of data

    To count how many appears on the destroyed list you can use

    =SUMPRODUCT(COUNTIF($C$2:$C$15,$B$2:$B$15))

    M.

  3. #3
    New Member
    Join Date
    Nov 2011
    Posts
    8

    Default Re: Using Vlookup to compare 2 columns of data

    Quote Originally Posted by Marcelo Branco View Post
    To count how many appears on the destroyed list you can use

    =SUMPRODUCT(COUNTIF($C$2:$C$15,$B$2:$B$15))

    M.
    Hi Marcelo,

    Thanks for suggesting the SUMPRODUCT but that will not cover the result I'm going for.
    In my example above, I have a "Destroyed?" column on the far left. Since I have a total of around 169,000 rows in this spreadsheet, it is important for me to know which items were destroyed. Once I get that "Destroyed?" properly populated, my next step is insert a PivotTable so that I summarize the data (e.g. Department 1 destroyed 50% of its items, Department 2 destroyed 2% of its items etc).

    If VLOOKUP is not the right approach to this, I'm more than happy to look at other approaches. An IF function perhaps?

  4. #4
    Board Regular Muzama Christo's Avatar
    Join Date
    Oct 2012
    Location
    India, Cochin
    Posts
    221

    Default Re: Using Vlookup to compare 2 columns of data

    Hi Mr Marcelo Branco,

    Slight clarification ...In the above thread posted by Mr TomTTT, how did he bring that scroll bar in it...It would be help for for me. Is it with the help of some Add in.

    Thanks in advane..Muz

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    7,798

    Default Re: Using Vlookup to compare 2 columns of data

    Quote Originally Posted by TomTTT View Post
    Hi Marcelo,

    Thanks for suggesting the SUMPRODUCT but that will not cover the result I'm going for.
    In my example above, I have a "Destroyed?" column on the far left. Since I have a total of around 169,000 rows in this spreadsheet, it is important for me to know which items were destroyed. Once I get that "Destroyed?" properly populated, my next step is insert a PivotTable so that I summarize the data (e.g. Department 1 destroyed 50% of its items, Department 2 destroyed 2% of its items etc).

    If VLOOKUP is not the right approach to this, I'm more than happy to look at other approaches. An IF function perhaps?
    Try this formula in A2 copied down

    =IF(ISNUMBER(MATCH(B2,$C$2:$C$169000,0)),"Yes","No")

    M.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    7,798

    Default Re: Using Vlookup to compare 2 columns of data

    Quote Originally Posted by Muzama Christo View Post
    Hi Mr Marcelo Branco,

    Slight clarification ...In the above thread posted by Mr TomTTT, how did he bring that scroll bar in it...It would be help for for me. Is it with the help of some Add in.

    Thanks in advane..Muz
    Maybe
    Go Advanced
    # (code tag)
    Paste

    Code:
    Destroyed? (i.e. Vlookup formula in this column) Eligible Serial Numbers Serial Numbers (destroyed)
    362351581 362351581
    362351582 362351582
    362351584 362351584
    362351585 362351585
    362351590 362351590
    362351603 362351603
    362351604 362351604
    362351605 362351605
    362351611 362351611
    362351612 362351612
    362351617 99379006
    362351618 99379007
    362351619 142353015
    362351620 142353016

  7. #7
    New Member
    Join Date
    Nov 2011
    Posts
    8

    Default Re: Using Vlookup to compare 2 columns of data

    Hi Marcelo,

    I should have mentioned that the my columns of data include text values (e.g. ABC1000) as well as numbers (e.g. 1000). To simplify this, I have set all of these fields to be TEXT.

    I changed the "ISNUMBER" portion of your formula to ISTEXT but I got confusing results. Namely, the formula is telling me "NO" when I can tell the values match just by looking at it. Does the approach you outlined work with TEXT? What am I missing?

    Is there some way I could post the Excel file here? That might be easier than me attempting to describe the problem.

  8. #8
    Board Regular Muzama Christo's Avatar
    Join Date
    Oct 2012
    Location
    India, Cochin
    Posts
    221

    Default Re: Using Vlookup to compare 2 columns of data

    @ TomTTT ...You can upload files using dropbox; then post the link here.

    Muz

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    7,798

    Default Re: Using Vlookup to compare 2 columns of data

    Quote Originally Posted by TomTTT View Post
    Hi Marcelo,

    I should have mentioned that the my columns of data include text values (e.g. ABC1000) as well as numbers (e.g. 1000). To simplify this, I have set all of these fields to be TEXT.

    I changed the "ISNUMBER" portion of your formula to ISTEXT but I got confusing results. Namely, the formula is telling me "NO" when I can tell the values match just by looking at it. Does the approach you outlined work with TEXT? What am I missing?

    Is there some way I could post the Excel file here? That might be easier than me attempting to describe the problem.

    Tom,
    Don't change the original formula. The formula works with text or numbers in columns B and C.
    The ISNUMBER checks, for each value in column B, if MATCH returns a number ( has found the same value in column C) or an error #NA (hasn't found the value in column C), irrespective if the values are text or numbers.

    M.

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    7,798

    Default Re: Using Vlookup to compare 2 columns of data

    Complementing my previous post
    Take a look at the Help file and look for MATCH
    You see that it doesn't return the value itself, it returns the relative position of the value.

    For example
    A1:A3
    John
    Mary
    Mike

    =MATCH("Mary",A1:A3,0)
    returns 2

    But if you try
    =MATCH("Richard",A1:A3,0)
    it returns #N/A

    M.

Page 1 of 2 12 LastLast

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