Link to home
Start Free TrialLog in
Avatar of tmajor99
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
ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Professor J
Professor J

create a folder named EE and download all the three attached files  saving them there.  then open file1 and then file2 and then file3.

enable data connection and then you would see how the formula works.
File1.xlsx
File2.xlsx
File3.xlsx
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.