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:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,275
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Try


Excel 2003
ABC
1NamePayrollCost Centre
2Peter100011111
3Aadi100021111
4Judy100031111
5Patrick100041111
6Ajay100052222
7Jason100062222
8Stuart100072222
Sheet1
Cell Formulas
RangeFormula
C2=INDEX(Sheet2!$A$2:$A$8,MATCH(B2,Sheet2!$B$2:$B$8,1))
C3=INDEX(Sheet2!$A$2:$A$8,MATCH(B3,Sheet2!$B$2:$B$8,1))
C4=INDEX(Sheet2!$A$2:$A$8,MATCH(B4,Sheet2!$B$2:$B$8,1))
C5=INDEX(Sheet2!$A$2:$A$8,MATCH(B5,Sheet2!$B$2:$B$8,1))
C6=INDEX(Sheet2!$A$2:$A$8,MATCH(B6,Sheet2!$B$2:$B$8,1))
C7=INDEX(Sheet2!$A$2:$A$8,MATCH(B7,Sheet2!$B$2:$B$8,1))
C8=INDEX(Sheet2!$A$2:$A$8,MATCH(B8,Sheet2!$B$2:$B$8,1))
 

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,866
Messages
5,834,073
Members
430,260
Latest member
MANICX100

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