This code will work

Sub lookup_data()
'fill sheet 2 with zeroes
With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B2:D" & LastRow).Value = 0
End With
With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Code = .Range("A" & RowCount)
Data = .Range("B" & RowCount)
Code_2 = .Range("C" & RowCount)
With Sheets("Sheet2")
'check if code_2 is in row1
Set R1 = .Rows(1).Find(what:=Code_2, _
LookIn:=xlValues, lookat:=xlWhole)
If Not R1 Is Nothing Then
'check if code 1 is in column A
Set C1 = .Columns("A:A").Find(what:=Code, _
LookIn:=xlValues, lookat:=xlWhole)
If Not C1 Is Nothing Then
.Cells(C1.Row, R1.Column) = Data
End If
End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub

"K" wrote:

I have data in sheet 1 (see below)
ROW A B C ------col (sheet 1)
1 CODE VALUE CODE_2
2 SS 10 GGG
3 TT 20 SSS
4 VV 30 TTT
5 ZZ 40 GGG
6 XX 50 GGG
7 RR 60 TTT
8 HH 70 TTT
9 JJ 80 GGG
10 KK 90 SSS
11 PP 100 TTT

in sheet 2 I have table and in cell B1 , C1 and D1 I have same Codes
as headings which are used in column C of sheet 1 (see below)

ROW A B C D----col (sheet 2)
1 CODE GGG SSS TTT
2 TT
3 SS
4 KK
5 PP

I want macro to check that if "GGG" which is in cell B1 of sheet 2 and
"TT" which is in cell A2 of sheet 2 come in same row in range "A2:C11"
of sheet 1 then get the value from column B of sheet 1 of that row and
put in cell B2 of sheet 2. And do same with other codes which are
"SS , KK , PP" in column A of sheet 2 and again same prossess with
codes "SSS and TTT". Basically i want macro to check 3 digit codes in
row 1 of column B to C and 2 digit codes from column A of sheet 2 into
sheet 3 and when 3 digit code and 2 digit code come in same row in col
A and C of sheet 3 then pick value form col B of that row and put in
sheet 2 against the codes.

macro should produce the result something like this (see below)

ROW A B C D----col (sheet 2)
1 CODE GGG SSS TTT
2 TT 0 20 0
3 SS 10 0 0
4 KK 0 90 0
5 PP 0 0 100