Excel 2010: Copy content of cell, based on lookup in another worksheet

I have 2 files. Both have Item # column and Description column.

In file 1, I need to search in file 2 for the Item #. If it finds it, I need to copy the content of the Description cell in file 1 and paste in the the description cell in file 2.

I would like to be able to drag the cell down through the spreadsheet so that it fills in the rest of the rows.

Any help is appreciated!
Curly101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
You should be able to useVLOOKUP for this, have you used VLOOKUP before?

regards, barry
0
Curly101Author Commented:
I have seen it, but cant figure out how to get it to copy the contents of the Description field if it finds a match in the Item # field.
0
barry houdiniCommented:
Say you have item numbers in column A in both files and in file 2 you also have the descriptions in column B then in file 1 B2 you can use a formula like this

=VLOOKUP(A2,'[file 2.xlsx]Sheet1'!$A$:$B$,2,0)

copy down column

[assumes both workbooks are open at this point - you can close the source workbook after the formula is set up]

If the columns are further apart you will have to adjust this.

If the description field is to the left of the item field then you need a different formula....

If you can't get that to work then please give more details - in which columns exactly is the data?

regards, barry

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Curly101Author Commented:
Barry,

I'm getting a #NA.

=VLOOKUP(A2,'[dataexport.xlsx]Products!$A$:$B$,2,0)

The description field that I need is Column C
0
barry houdiniCommented:
And where are the item number, column A?

In VLOOKUP the "lookup value" (in this case A2) is looked up in the first column of the "lookup range" (in this case '[dataexport.xlsx]Products!$A$:$B$, so the first column is column A....and the "column index" (2 here defines which column to return a value from...so if you need to lookup the value in column A and return the value from C you need to change the range and make the column index 3, i.e.

=VLOOKUP(A2,'[dataexport.xlsx]Products!$A$:$C$,3,0)

regards, barry
0
Curly101Author Commented:
Column A is correct for both files
0
barry houdiniCommented:
Ok so that last formula should work for you assuming there are exact matches with the item #s....

barry
0
Curly101Author Commented:
Sorry, Barry. Not working.

When I paste in the formula, I'm getting an error that "The name that you entered is not valid." Does not begin with a letter or an underscore...the name contains a space or other invalid character.

When I hit Enter, it highlights: '[dataexport.xlsx]Products


I am putting this formula in the destination file, not the source, right? I am putting it on row2, in the cell where I want the description to be copied to.

Thanks

0
barry houdiniCommented:
Sorry, the are two typos in there - you need to remove the  apostrophe before the filename  (in my original there are apostrophes around the file/sheet names because the file name contained a space - you don't need those)...and there are too many $ signs in my original - try this version

=VLOOKUP(A2,[dataexport.xlsx]Products!$A:$C,3,0)

regards, barry
0
Curly101Author Commented:
That worked Barry...thanks. But when I copied the field to the next cell down, it put in the same info as the original. How do I get it to move to the next item #?
0
barry houdiniCommented:
That should work OK, did the A2 in the formula change to A3? You may have calculation set to "manual" - re-calculate by pressing F9 key.

To set to automatic...... on the "formulas" tab at the right you have a "Calculation Options" button - click that and set to "Automatic"

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Curly101Author Commented:
Barry,

All worked perfectly. Thanks for everything!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.