lookup, copy and paste. (with nice example :)

Leafgreen

Leafgreen

New Member
Joined
Jan 12, 2010
Messages
39
Hello,
I have two worksheets. ws1 has two columns of values, colA and colB. ws2 has one column of values. Values in ws1-colB match some values in ws2.

For all the values that match between ws1-colB and ws2, I want to copy and paste the values from ws1-colB for those matching value rows. :huh: Example, I guess you might want! :)

ws1...
Excel Workbook
AB
3881930115461154
4881930115531155
5881930115601156
6881930115771157
7881930115911159
8881930116381163
9881930116451164
10881930116521165
11881930116691166
12881930116761167
13881930116831168
14881930118331183
...

ws2...
Excel Workbook
A
37703
47750
57751
67752
77753
87111
97120
107208
117300
127351
131183
147800
...

So you can see that the only matching value between the two worksheets is ws2:A13. So I'd like the result...the solution :biggrin:...to be this (in a new set of columns or ws)...
Excel Workbook
DE
37703
47750
57751
67752
77753
87111
97120
107208
117300
127351
13118388193011959
147800
...

Excel 2007. TIA!!
Leafgreen
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Oops I made a critical error in the solution description. Instead of "(in a new set of columns or ws)..." I want the pasted value in ws2 to be on the same row as the matching value. The solution example is correct, just the quote above is wrong :0
 
Upvote 0
Where is the value 88193011959 in E13 coming from?
 
Upvote 0
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 84px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1154</TD><TD style="TEXT-ALIGN: right">88193011546</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1155</TD><TD style="TEXT-ALIGN: right">88193011553</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">1156</TD><TD style="TEXT-ALIGN: right">88193011560</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">1157</TD><TD style="TEXT-ALIGN: right">88193011577</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">1159</TD><TD style="TEXT-ALIGN: right">88193011591</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">1163</TD><TD style="TEXT-ALIGN: right">88193011638</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">1164</TD><TD style="TEXT-ALIGN: right">88193011645</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">1165</TD><TD style="TEXT-ALIGN: right">88193011652</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">1166</TD><TD style="TEXT-ALIGN: right">88193011669</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">1167</TD><TD style="TEXT-ALIGN: right">88193011676</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">1168</TD><TD style="TEXT-ALIGN: right">88193011683</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">1183</TD><TD style="TEXT-ALIGN: right">88193011833</TD></TR></TBODY></TABLE>

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 35px"><COL style="WIDTH: 84px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">7703</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">7750</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">7751</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">7752</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">7753</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">7111</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">7120</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">7208</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">7300</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">7351</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">1183</TD><TD style="TEXT-ALIGN: right">88193011833</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">7800</TD><TD>#N/A</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B2</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B3</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B4</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B5</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B6</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B7</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B8</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B9</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B10</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B11</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR><TR><TD>B12</TD><TD>=VLOOKUP(A:A,Sheet1!A:B,2,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Try this
 
Upvote 0
Formula in B3 copied down

Excel Workbook
AB
37703 
47750
57751
67752
77753
87111
97120
107208
117300
127351
13118388193011833
147800
Sheet2
 
Upvote 0
You may need to adjust the row ranges in my formula but since you have Excel 2007, you can use the new, more efficient, IFERROR function.

Excel Workbook
AB
37703 
47750
57751
67752
77753
87111
97120
107208
117300
127351
13118388193011833
147800
Sheet2
 
Upvote 0
You must log in or register to reply here.

Similar threads

Logit
  • Question Question
Search In Other Workbook - Copy/Paste Offset Data To First Workbook - Repeat Process
Replies
7
Views
354
Logit
Logit
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
663
myall_blues
myall_blues
B
  • Question Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
569
B-radK
B
D
  • Question Question
Lookup value based on two column criteria
Replies
3
Views
470
Cubist
Cubist
A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,224,598
Messages
6,179,820
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top