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

[Casim]

Hi All,

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

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

	A	B	C
	Name	Payroll	Cost Centre
1	Peter	10001	{get cost centre using payroll from sheet2}
2	Aadi	10002
3	Judy	10003
4	Patrick	10004
5	Ajay	10005
6	Jason	10006
7	Stuart	10007

<tbody>
</tbody>

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

	A	B	C
	Cost Centre Number	Payroll
1	1111	10001
2	1111	10002
3	1111	10003
4	1111	10004
5	2222	10005
6	2222	10006
7	2222	10007

<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.

 

[Michael M]

Try

Excel 2003
	A	B	C
1	Name	Payroll	Cost Centre
2	Peter	10001	1111
3	Aadi	10002	1111
4	Judy	10003	1111
5	Patrick	10004	1111
6	Ajay	10005	2222
7	Jason	10006	2222
8	Stuart	10007	2222
Sheet1

Cell Formulas
Range	Formula
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]

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