?
Solved

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

Posted on 2011-10-03
12
Medium Priority
?
216 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:Curly101
  • 6
  • 6
12 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36904515
You should be able to useVLOOKUP for this, have you used VLOOKUP before?

regards, barry
0
 

Author Comment

by:Curly101
ID: 36904592
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36904720
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Curly101
ID: 36904818
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36904859
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
 

Author Comment

by:Curly101
ID: 36904907
Column A is correct for both files
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36904971
Ok so that last formula should work for you assuming there are exact matches with the item #s....

barry
0
 

Author Comment

by:Curly101
ID: 36905039
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36905092
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
 

Author Comment

by:Curly101
ID: 36905179
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 36905397
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
 

Author Comment

by:Curly101
ID: 36905437
Barry,

All worked perfectly. Thanks for everything!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question