This macro should do it.
Sub copynums()
mc = "f"
For i = 2 To 14
'MsgBox Cells(i, mc)
With Sheets("sheet4") 'destinationsheet
destcol = .Rows("1:1").Find(What:=Cells(i, mc), _
After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext).Column
'MsgBox destcol
..Cells(i, destcol) = Cells(i, mc).Offset(, -4)
End With
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
I have data in sheet 1 (please see below)

A B C D E F ---------Columns
2 50 200801
2 50 200806
4 25 200803
4 25 200806
5 7.72 200801
5 7.88 200802
5 7.13 200803
6 100 200806
7 8 200802
7 9 200803
8 100 200808
9 3 200801
9 0.26 200802


and I have table in sheet 2 (please see below)


A B C ---------Columns
NO 200801 200802
2
4
5
6
7
8
9


I want some sort of lookup formula in column B2 and C2 of sheet 2
(which I can drag down) which should look values of cell A2 and B1 of
sheet 2 in column A to column F of sheet 1 and bring value from
column
C of sheet 1 to cell B2 of sheet 2 and then same thing in cell C2 of
sheet 2
Result of formula should look like this (please see below)


A B C ---------Columns
NO 200801 200802
2 50
4
5 7.72 7.88
6
7 8
8
9 3 0.26


Please if any body can help