We help IT Professionals succeed at work.

MS Excel VLookup and copy from one worksheet to another

ckwillGWU
ckwillGWU asked
on
I have a workbook with multiple worksheets. I want to be able to find the text in Worksheet B that matches the text string in a cell in Worksheet A. Once the exact text string is located in Worksheet B that matches Worksheet A, then I want that cell and the cell next to eat (e.g. A1 and A2) from Worksheet B copied over to Worksheet A next to the matching text. Note: There are more rows of data in Worksheet B than in Worsheet A. Also, if text is not found in Worksheet B, then leave the cells in Worksheet A, next to the orignial text string can be left blank. Also, if possible to have the merged data to go to another workheet (Worksheet C) that is combined would work also.

I've attached some dummy data in an excell spreadsheet that I can use if the VB Script, macro's etc are inserted / updated.

Thanks a lot, Chris
OneFile.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

to find the description, use in C2 on Main sheet:

=iferror(VLOOKUP(A2,data!A1:$B$37,2,0),"")

copy down.

There is really no point in looking up the "Matching" text string in B, since if it is found, it will always be the same as the value already displayed in A, but if you really want to duplicate it with a lookup, use in B2 and copy down

=iferror(VLOOKUP(A2,data!A1:$B$37,1,0),"")


You can copy the results and use Paste Special > Values on the Combined sheet, or for a dynamic reference use in A1 of Combined sheet

=Main!A1

copy down and across.

cheers, teylyn
Some refinements to teylyn's formula:

=iferror(VLOOKUP(A2,data!$A$1:data!$B$B,2,0),"")

Reasoning:
- Change A1 to $A$1 - if not changed, copying this formula down will change the array to A2:$B$37; copying it across will change array to B1:$B$37
- Change $B:$37 to data!:$B:$B - allows the length of the reference array to be dynamic
- Change 1 to 2 - When set as 1, it would pull the matched value, not the desired description
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Mark,

re >> Change A1 to $A$1 - if not changed, copying this formula down will change the array to A2:$B$37; copying it across will change array to B1:$B$37
Thanks, I missed that one.

re >> Change $B:$37 to data!:$B:$B - allows the length of the reference array to be dynamic
I disagree. Using whole column references is bad practice and can lead to performance issues with big datasets.

re >>  Change 1 to 2 - When set as 1, it would pull the matched value, not the desired description
Read my post again.

cheers, teylyn
Teylyn,

re >> whole column references - I agree with your disagree in principle, but I don't get the sense that this list would grow beyond a finite set of codes to be matched.  If Chris can specify the list, then the $B$x range could be more appropriately targeted.  However, if not $B$B, then perhaps $B$100 to allow some flexibility for periodically adding codes without having to update the formula and re-drag each time another code is added to the list

re >> Change 1 to 2
Sorry, I was looking at the second formula when I made reference to this.  Your first formula is correct in this regard.  mea culpa

Thanks, Mark
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Mark,

re the column references: people post small data samples. We have no idea how big their final dataset may be. The preferred approach would be dynamic range names that grow with the data and feed only the populated cells to the formula.

cheers, teylyn
re >> dynamic ranges
Agreed, which is probably the solution that should be posted for this issue

Chris,
Reference the following link with regard to information about creatin a dynamic range for the "data" tab: http://www.automateexcel.com/2008/08/23/introduction-to-dynamic-ranges/

I created a Range named Code_Lookup in your spreadsheet example.  The formula for the "Refers to" section of the range definition is: =OFFSET($A$1,0,0,COUNTA($B:$B),2)

Once the range is created, paste the following formula into cell C2 on the "Main" tab and then copy down: =IFERROR(VLOOKUP(A2,Code_Lookup,2,0),"")

Now you can add additional rows of lookup references on the data tab without needing to adjust the range of the VLOOKUP. Copy-of-OneFile.xlsx Copy-of-OneFile.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Mark, a tip: avoid Offset. It's volatile. Use Index instead.
Again, agree in principle, but at some sacrifice to practicality/readability.  I think OFFSET is fine for the example presented, but if a more robust solution is needed (because the data set is expected to grow significantly), then the following alternative will work:

Range Refer To: =COUNTA(data!$A:$A)   (created as range name: "Last_Row")
VLOOKUP: =IFERROR(VLOOKUP(A2,data!$A$1:INDEX(data!$B:$B,Last_Row),2,0),"")

I added this alternative formula in column D of the attached spreadsheet

(Thanks for the additional "push", teylyn...the back-and-forth dialogue is what makes this such an excellent forum) Copy-of-OneFile.xlsx

Author

Commented:
Thanks all. I've plugged the one (VLOOKUP)  formula from Mark, but Mark -- I'm a little confused as how to make it work and the =COUNT formula goes? When I use the VLOOKUP in a separate spreadsheet alone, it doesn't work.
You need to create a range.  What version of Excel are you running?

Author

Commented:
2010
Reference this URL for information about creating the range in 2010: http://www.addictivetips.com/microsoft-office/excel-2010-name-range/

Once you get to the point of creating the range, the =COUNTA(data!$A:$A) formula goes into the field, "Refers to" (see the Range Setup Screenshot).  Be sure to name the range, "Last_Row" (or adjust the VLOOKUP to use whatever name you choose to use)

Range Setup Screenshot

Author

Commented:
I was able to get the INDEX version running; however some of the cells that should have text in them are coming up empty. When I go to the tab with the data and do a find - the information is there, but not importing over to the main spreadsheet. Should there be something in the Name Manager for INDEX as well?
Make sure you have all the $ symbols in place in the formula.  If you're missing them, your VLOOKUP formula may be changing in an undesired way as you copy it down.  The result is that your range would start one row lower for each copy down.  For the ones that are missing, do they match near the top of the array?

Author

Commented:
Thanks Mark, The issue was that the data received wasn't clean. There were merged cells and blank rows in between. Not sure if that should have mattered in the formula or not, but once cleaned up - it fixed the problem. Again, thanks for all your help. -- Chris

Author

Commented:
Teylyn - thank you as well.

Author

Commented:
Great tag team base. Really enjoyed the assistance from both of Teylyn  and Mark.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.