Results 1 to 2 of 2
2012-10-11, 15:12 #1
- Join Date
- Oct 2003
- Colorado, USA
- Thanked 1 Time in 1 Post
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:
Is something like this possible?Stealing from one is call plagiarism, stealing from many is called research. ;-)~
2012-10-19, 17:59 #2
- 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.