1

I have 3 Excel files. Excel file 1 and Excel file 2 contains:

  • Fixed data in column A
  • Different values in column H.

Excel file 3 (dashboard) contains the fixed data found in column A of the 2 files, and column B, C, D should return the values of column H.

The data in column A can be moved to different cells so it should search for the exact match regardless of cell.

Excel File 1

Col. A          Col. H                                    
Thomas Moore        0
Albert Einstein    23
John Doe            8

Excel File 2

Col. A          Col. H                                    
Thomas Moore       10
Albert Einstein    98
John Doe           50

Excel File 3

Col. A          Col. B     Col. C                                    
Thomas Moore        0        10
Albert Einstein    23        98
John Doe            8        50

Column A (Same values in Excel file 1 and Excel file 2) Column B (Excel file 1 Col H) Column C (Excel file 2 Col H)

In file 3, everytime it matches the exact values in other files Col A, it returns the corresponding value in H (works like a summary worksheet).

Improve this question
0

1 Answer 1

Reset to default
0

You can use Vlookup in File 3. If the first row has column labels, and the first column is in row 2, use this formula in B2

=VLOOKUP(A2,'[File 1.xlsx]Sheet1'!$A$1:$H$1000,8,FALSE)

In C2 use

=VLOOKUP(A2,'[File 2.xlsx]Sheet1'!$A$1:$H$1000,8,FALSE)

Adjust the number of rows in the formula to your data set and copy the formulas down.

Improve this answer
7
  • I applied this =vlookup(A2;'[File 1.xlsx]Summary of Main'!A:A,8,false) and error message what does 8,false means? it is highlighting this
    – Myk
    May 25, 2015 at 8:26
  • I don't think you can use whole columns in Excel 2007 for Vlookup. Try to use a range with a limited number of rows. 8 instructs to return the value from the 8th column of the table (column H) and False is the instruction for an approximate match. You want an exact match, so the parameter needs to be False.
    – teylyn
    May 25, 2015 at 10:26
  • i used this =VLOOKUP(A2;'[File 1.xlsx]Summary of Main'!$A$2:$A$6;8;FALSE) and now giving me #REF! in B2 cell
    – Myk
    May 25, 2015 at 10:35
  • You need to include the whole table not just $A$2:$A$6. Excel looks for the 8th column and this only has one column so it fails. Change that range to $A$2:$H$6.
    – gtwebb
    May 25, 2015 at 16:58
  • My formula says $A$1:$H$1000, not $A$1:$A$1000. Why don't you start with the formula I supplied and just change the file and sheet name? Every other change you make will be likely to break the formula.
    – teylyn
    May 25, 2015 at 20:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .