tmajor99
asked on
EXCEL - Vlookup
Can someone show me how to do a simple EXCEL Vlookup. I have two files and each file contains a column called Product_ID. I want to return some columns from both files to form a new file.
File 1 -
ProductID ProductDescription
12345555 Widget
File 2 -
ProductID ProductCost
12345555 3.99
File 3 - Merge of files 1 and 2
ProductID ProductDescription ProductCost
12345555 Widget 3.99
File 1 -
ProductID ProductDescription
12345555 Widget
File 2 -
ProductID ProductCost
12345555 3.99
File 3 - Merge of files 1 and 2
ProductID ProductDescription ProductCost
12345555 Widget 3.99
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you have all the same product ID’s in both files, then one of the shorter and easier ways to do this would be the following.
1. Copy File 1 and name it File 3.
2. From File 2, copy the data from the worksheet that contains the ProductID and ProductCost into a blank worksheet in File 3. You can name that new worksheet in File 3 anything you want, but for the sake of this example, I will name it “File2Info”.
3. The following assumes that all headings are in row 1. All the data columns are in columns A and B. For the sake of the formula, I will assume that the data does not extend beyond row 500. Anything different from these assumptions, you will need to alter the formula accordingly.
4. On the worksheet in File 3 that contains the ProductID and the ProductDescription, in cell C1, type in your heading “ProductCost”. In cell C2 type the following formula
=VLOOKUP(A2,File2Info!$A$2 :$B$500,2, FALSE)
5. Copy this formula down to row 500 (or whatever is your last row of data).
6. Once everything looks good, you can copy the new column C onto itself as “values only.”
7. Then you can delete the worksheet called “File2Info”.
8. File 3 should now be the way you want it and ready to go.
1. Copy File 1 and name it File 3.
2. From File 2, copy the data from the worksheet that contains the ProductID and ProductCost into a blank worksheet in File 3. You can name that new worksheet in File 3 anything you want, but for the sake of this example, I will name it “File2Info”.
3. The following assumes that all headings are in row 1. All the data columns are in columns A and B. For the sake of the formula, I will assume that the data does not extend beyond row 500. Anything different from these assumptions, you will need to alter the formula accordingly.
4. On the worksheet in File 3 that contains the ProductID and the ProductDescription, in cell C1, type in your heading “ProductCost”. In cell C2 type the following formula
=VLOOKUP(A2,File2Info!$A$2
5. Copy this formula down to row 500 (or whatever is your last row of data).
6. Once everything looks good, you can copy the new column C onto itself as “values only.”
7. Then you can delete the worksheet called “File2Info”.
8. File 3 should now be the way you want it and ready to go.
enable data connection and then you would see how the formula works.
File1.xlsx
File2.xlsx
File3.xlsx