Help with excel formula (copy data from one sheet to another when value matches)

B

BravoBravo

New Member
Joined
Aug 10, 2008
Messages
14
Hi all,

This is likely to be a pretty simple formula for most of you - your assistance would be very much appreciated!!

I have two sheets (sheet 1 and sheet 2). Sheet 2 has a range of data about employees. Column A contains a unique reference number with the rest of the row (Column B - Column X) containing corresponding data about that empoyee.

When I enter that unique reference number in Sheet 1, Column A, and matches the value in Sheet 2, Column A; I would like the rest of the corresponding row data (Column B - Column X) from Sheet 2 to populate 'automatically' (copied) in Sheet 1.

I hope my explaination is clear??!

Thanks in advance...
 
Sort by date Sort by votes
BravoBravo said:
Thanks Aladin Akyurek, I've played around and it seems to be working.

What can I do with the '4' in cell C3 though - it causes me problems with formatting and adding additional rows of data...
Click to expand...

If you want multiple employees...

Sheet1, A1:G3...

<TABLE style="WIDTH: 369pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=490 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=70>Emp Ref</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=38>Pos</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=70>ADDRESS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=85>TELEPHONE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=56>DOB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=112>CLASSIFICATION</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=59>SALARY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>45678</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>6 Black St</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>456789</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>27395</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>2.1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>70000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>67890</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>2 Black St</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>678901</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>28126</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>3.1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>80000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD></TR></TBODY></TABLE>

B2, copy down:

=MATCH(A2,Sheet2!$A$2:$A$7,0)

C2, copy across and down:

=INDEX(Sheet2!B$2:B$7,$B2)
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
I have followed your advice (I think), but for some reason Shhet 1, column C2 and D2 (using your table above) are data from Shhet 2, cell C11 and D11??? Sheet 1, E2 and F2 are correct.

When I try to copy down the formula I get #NA Value Not Available Error.

Would you be willing to PM me your email and I'll send you a version???

Thanks again
 
Upvote 0
BravoBravo said:
I have followed your advice (I think), but for some reason Shhet 1, column C2 and D2 (using your table above) are data from Shhet 2, cell C11 and D11??? Sheet 1, E2 and F2 are correct.

When I try to copy down the formula I get #NA Value Not Available Error.

Would you be willing to PM me your email and I'll send you a version???

Thanks again
Click to expand...

Try the whole thing in a fresh workbook, see how it works, then try to adapt to your real workbook.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Table matrix value swapping
Replies
2
Views
53
Alex Blakenburg
A
J
  • Question
INDEX MATCH from two sheets onto third sheet?
Replies
1
Views
95
etaf
etaf
P
  • Question
Matching data in sheets and copying data.
Replies
0
Views
135
Pophil
P
J
  • Question
Sum data in a range based on a column and row and an equation
Replies
1
Views
141
bobaol
B
D
  • Question
Copy the value of a cell onto another worksheet based on the dropdown selection
Replies
0
Views
49
denissimo
D

Forum statistics

Threads
1,214,983
Messages
6,122,582
Members
449,089
Latest member
Motoracer88

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