Results 1 to 8 of 8

Vlookup, comparing two columns, output matches to a 4th column

This is a discussion on Vlookup, comparing two columns, output matches to a 4th column within the Excel Questions forums, part of the Question Forums category; Hello, I'm using Excel 2003 and am trying to consolidate data from two customer spreadsheets into one. What it boils ...

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Default Vlookup, comparing two columns, output matches to a 4th column

    Hello,

    I'm using Excel 2003 and am trying to consolidate data from two customer spreadsheets into one. What it boils down to is this, lets say I have these four columns in Excel:



    What I need is a formula that will compare column C to column B, when it finds a match, copy A to D.

    I've been trying to use Vlookup but have not figured it out. Any ideas?

    Thanks!

  2. #2
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default Re: Vlookup, comparing two columns, output matches to a 4th column

    Try in D1

    =IF(B1=C1,A1,"") and copy down.
    "I'm not a perfectionist; I'm just perfect."

  3. #3
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,795

    Default Re: Vlookup, comparing two columns, output matches to a 4th column

    Hi,

    Perhaps try Index/Match like this:

    Sheet2
    ABCD
    1Cust ID#Company Name (source 1)Company Name (source 2)Cust ID# Output
    21NamcoNamco1
    32AtariAtari2
    43NintendoNintendo3
    54NeoGeoSony5
    65Sony
    Excel 2010

    Worksheet Formulas
    CellFormula
    D2=INDEX(A$2:A$6,MATCH(C2,B$2:B$6,0))


  4. #4
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Default Re: Vlookup, comparing two columns, output matches to a 4th column

    Re: BenMiller

    Unfortunately column C and B get out of sync quickly because B has many duplicates / old entries.

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Default Re: Vlookup, comparing two columns, output matches to a 4th column

    Re: circledchicken


  6. #6
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,795

    Default Re: Vlookup, comparing two columns, output matches to a 4th column

    Haha, your welcome!

  7. #7
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Default Re: Vlookup, comparing two columns, output matches to a 4th column

    Follow up to this question. What if I wanted to show all the cells in B that do not have a match in C?

  8. #8
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,795

    Default Re: Vlookup, comparing two columns, output matches to a 4th column

    Hi,

    One way might be:

    Sheet1
    ABCD
    1Cust ID#Company Name (source 1)Company Name (source 2)Not in C
    21NamcoNamco1
    32AtariAtari2
    43NintendoNintendo3
    54NeoGeoSony#N/A
    65Sony4
    Excel 2010

    Worksheet Formulas
    CellFormula
    D2=MATCH(B2,C$2:C$6,0)
    • Drag down the formula in D2 to the end of your data.
    • Filter column D for the #N/A's

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