Linking Data according to names

[lensman]

Hallo, this is my first time here.

I basically have two seperate worksheets.

The one looks like this:

query.xls
	B	C	D	E
1	CUST.COD.	CUSTOMER	LAB.CODE	LABNAME
2	P450.001	COLLEGEUNIVERSITY	239	LAB1
3	P450.001	COLLEGEUNIVERSITY	533	LAB2
4	P450.001	COLLEGEUNIVERSITY	645	LAB3
5	P450.001	COLLEGEUNIVERSITY	423	LAB4
6	P450.001	COLLEGEUNIVERSITY	132	LAB5
7	P450.001	COLLEGEUNIVERSITY	455	LAB6
8	P450.001	COLLEGEUNIVERSITY	123	LAB7
9	P450.001	COLLEGEUNIVERSITY	542	LAB8
10	P450.001	COLLEGEUNIVERSITY	23	LAB9
11	P450.001	COLLEGEUNIVERSITY	65	LAB10
12	P450.001	COLLEGEUNIVERSITY	98	LAB11
13	P450.001	COLLEGEUNIVERSITY	421	LAB12
14	P450.001	COLLEGEUNIVERSITY	532	LAB13
Sheet1

The other looks like this:

query.xls
	C	D	E	F
1	LAB.CODE	VALUE
2	23	4324,89
3	65	3234,65
4	98	13213,89
5	123	435332,9
6	132	312312,7
7	239	3432
8	421	23423,9
9	423	34321,65
10	455	23121,04
11	532	12312,9
12	533	2312,67
13	542	342421,2
14	645	3432,09
Sheet2

Basically the situation goes like this: I am not allowed to sort the data in the first worksheet. I am supposed to link the data that exists in the second one according to the lab code, so that the final document looks like this:

query.xls
	B	C	D	E	F
1	CUST.COD.	CUSTOMER	LAB.CODE	LABNAME	VALUE
2	P450.001	COLLEGEUNIVERSITY	239	LAB1	3432
3	P450.001	COLLEGEUNIVERSITY	533	LAB2	2312,67
4	P450.001	COLLEGEUNIVERSITY	645	LAB3	3432,09
5	P450.001	COLLEGEUNIVERSITY	423	LAB4	34321,65
6	P450.001	COLLEGEUNIVERSITY	132	LAB5	312312,7
7	P450.001	COLLEGEUNIVERSITY	455	LAB6	23121,04
8	P450.001	COLLEGEUNIVERSITY	123	LAB7	435332,9
9	P450.001	COLLEGEUNIVERSITY	542	LAB8	342421,2
10	P450.001	COLLEGEUNIVERSITY	23	LAB9	4324,89
11	P450.001	COLLEGEUNIVERSITY	65	LAB10	3234,65
12	P450.001	COLLEGEUNIVERSITY	98	LAB11	13213,89
13	P450.001	COLLEGEUNIVERSITY	421	LAB12	23423,9
14	P450.001	COLLEGEUNIVERSITY	532	LAB13	12312,9
Sheet1

Is there an easy way to do this?

 

[ken2step]

Pivot table would work great for that

Ken

 

[lensman]

The problem is that I am not allowed to create a new worksheet and I can not edit the entry order. Another problem is that there are numerous subheadings, so creating a pivot table brings out a huge mess.

I have to somehow link the lab code in worksheet 1, to the lab code in worksheet 2 and have it display the value somehow.

Please, I am lost...

 

[Aladin Akyurek]

Is it the case that the data on Sheet1 is in ascending order on CUST.COD.?

 

[lensman]

No, they are not. The subcategories are related to the city where each client is located.

Basically I need a code that will check out the Lab.Code in worksheet 1, then look it up in Worksheet 2 and display the Lab Value right next to it.

 

[Aladin Akyurek]

No, they are not. The subcategories are related to the city where each client is located.

Basically I need a code that will check out the Lab.Code in worksheet 1, then look it up in Worksheet 2 and display the Lab Value right next to it.

Is LAB.CODE on Sheet2 in ascending order? This is actually the question I wanted to pose.

 

[lensman]

Yes, it is...

 

[Aladin Akyurek]

Yes, it is...

Perfecto...

In F2 on Sheet1 enter & copy down:

=IF(LOOKUP(D2,Sheet2!$A$2:$A$14)=D2,LOOKUP(D2,Sheet2!$A$2:$B$14),"")