Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Colorado, USA
    Thanked 1 Time in 1 Post

    Question Compare multiple (non adjacent) columns between 2 sheets in Excel 2007

    This is a little ugly for what I am trying to do (reconcile data between file 1 & file 2). file 1 has many tabs that I will be comparing different columns to against only 1 sheet in file 2, but the layout of each tab is different in file 1.

    In File 1 sheet1:
    column L listing a order #
    column G listing a serial #
    column F listing a material # (but not needed for vlookup)
    column E listing the ordered qty

    In File 2 sheet1:
    Column C listing a order #
    Column J listing a Serial #
    Column G listing a material # (1 value I want returned)
    Column I listing the shipped qty (1 value I want returned)

    As I don't have control over the columns order between these 2 sheets, I am trying to use either a Vlookup, INDEX or even sumifs to compare columns L & G in the 1st file to columns C & J in the 2nd file. When these 2 match, then I would want to return the value in column G in the 2nd file.

    I know I can create a column in each file that concatenates the order & serial # and then do a simple Vlookup between the 2 files, but I am trying to get away from that so I don't need to create several custom column groupings in file 2 for each of the different tabs in file 1.

    So from file 1 sheet 1 my non correct formula to return value from column I in file 2:
    =vlookup(L2&g2,'[file 2.xlsx]sheet1'!c2:c100&j2:j100,6)

    Is something like this possible?
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    If there is only 1 value you can use something like:
    =SUMPRODUCT(('[file 2.xlsx]Sheet1'!$C$2:$C$100=L2)*('[file 2.xlsx]Sheet1'!$J$2:$J$100=G2)*'[file 2.xlsx]Sheet1'!$I$2:$I$100)

    Last edited by sdckapr; 2012-10-19 at 18:10.

Posting Permissions

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