1. Find value1 (sheet2) & write corresponding col value to corresponding col of value1 (Sheet1)

Casim

New Member
Joined
Sep 22, 2011
Messages
5
Hi All,

Firstly sorry about the convoluted title - i really had trouble trying to narrow the issue without pictures :confused:

I hoping someone may be able to help me with the following task.


Sheet1
has a list of users with their corresponding payroll numbers.

Basically I just want to write their corresponding cost centre from Sheet2 into column C of Sheet1

ABC
NamePayrollCost Centre
1Peter10001{get cost centre using payroll from sheet2}
2Aadi10002
3Judy10003
4Patrick10004
5Ajay10005
6Jason10006
7Stuart10007

<tbody>
</tbody>



Sheet2 has an array of cost centres & corresponding payroll numbers.


ABC
Cost Centre NumberPayroll
1111110001
2111110002
3111110003
4111110004
5222210005
6222210006
7222210007

<tbody>
</tbody>

Yes I know this is probably Excel 101 but I have tried to research it with vLookup, VLookup Match and VB - I'm just not an Excel person.

Thanks in advance.
Casim.
 
Last edited:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,715
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">Payroll</td><td style=";">Cost Centre</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Peter</td><td style="text-align: right;;">10001</td><td style="text-align: right;;">1111</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Aadi</td><td style="text-align: right;;">10002</td><td style="text-align: right;;">1111</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Judy</td><td style="text-align: right;;">10003</td><td style="text-align: right;;">1111</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Patrick</td><td style="text-align: right;;">10004</td><td style="text-align: right;;">1111</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Ajay</td><td style="text-align: right;;">10005</td><td style="text-align: right;;">2222</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Jason</td><td style="text-align: right;;">10006</td><td style="text-align: right;;">2222</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Stuart</td><td style="text-align: right;;">10007</td><td style="text-align: right;;">2222</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet2!$A$2:$A$8,MATCH(<font color="Red">B2,Sheet2!$B$2:$B$8,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet2!$A$2:$A$8,MATCH(<font color="Red">B3,Sheet2!$B$2:$B$8,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet2!$A$2:$A$8,MATCH(<font color="Red">B4,Sheet2!$B$2:$B$8,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet2!$A$2:$A$8,MATCH(<font color="Red">B5,Sheet2!$B$2:$B$8,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet2!$A$2:$A$8,MATCH(<font color="Red">B6,Sheet2!$B$2:$B$8,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet2!$A$2:$A$8,MATCH(<font color="Red">B7,Sheet2!$B$2:$B$8,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet2!$A$2:$A$8,MATCH(<font color="Red">B8,Sheet2!$B$2:$B$8,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Casim

New Member
Joined
Sep 22, 2011
Messages
5
Firstly - thanks Michael, for your reply. It is much appreciated.

I tried the solution on my spreadsheet and it works a treat until i try to adapt it to my data.

So I tried to cut and paste my values into the fields or your working example and it failed.

So I'm guessing my data is carrying some junk with it or something.

I tried cleansing it by pasting into Notepad and then pasting into Excel and still returned the last value in the column - strange.
I also tried exporting the data as a tab deliminated TXT file and then re-importing but still it failed.
I've tried changing cell format

I'll keep trying - any suggestions?
Casim
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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
Top