Excel Look up and Return Data

[Alton B. Wilson]

Hello all,

I have a "data table" in one Excel file (call it DATA, the table is
sorted on a field called 'CODE') and a second Excel file (call it
FORMULA). I need to lookup information from the DATA table and return
it to the FORMULA file, this will be done by matching a Raw Material
Code number from the FORMULA file to the DATA table. When the code is
matched, I would like to return 5 consecutive cells (in 5 consecutive
columns) of data to the FORMULA file. Currently, I am using 5 vLookup
functions to do this in the 5 consecutive cells. I am doing this to try
to speed up the process.

I think this can be done, but I have never used arrays of data or
functions.


Data Table

CODE Size Cost1 Cost2 Weight Color

100 23 2 13 50 Blue
200 45 5 88 60 Green
300 67 8 74 70 Yellow
400 88 9 99 80 Black
500 98 11 66 90 White



Formula File

CODE Size Cost1 Cost2 Weight Color

Thanks for any help.

Regards,

-- Alton

 

[Bernard Liengme]

Look like a task for INDEX with MATCH


Data Item cherry 7
code data1 6
apple 4 5
apricot 5 3
banana 3 4
cherry 7
damson 6
orange 5
peach 3
pear 4
plum 2

The range apple...plum is called CODE
The 4....2 is called Data1
The cell with 'cherry' is called ITEM
The formula in next cell F1 is =INDEX(data1,MATCH(Item,code,1)+ROW()-1)
Copy this down
best wishes

 

[muddan madhu]

Look like a task for INDEX with MATCH

      Data   Item cherry 7
      code data1    6
      apple 4    5
      apricot 5    3
      banana 3    4
      cherry 7
      damson 6
      orange 5
      peach 3
      pear 4
      plum 2

The range apple...plum is called CODE
The 4....2 is called Data1
The cell with 'cherry' is called ITEM
The formula in next cell F1 is =INDEX(data1,MATCH(Item,code,1)+ROW()-1)
Copy this down
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email













- Show quoted text -

i think this might help u, try out if i am wrong guide me

Dim va1 As String
Application.ScreenUpdating = False

Sheets("Formula Table").Select
Range("a2").Select
Selection.Copy
va1 = ActiveCell.Value
va1 = Range("a2")
ActiveSheet.Previous.Select
Range("A:A").Select
Cells.Find(what:=va1, after:=ActiveCell, LookIn:=xlFormulas,
lookat:= _
xlPart, searchorder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, searchformat:=False).Activate
ActiveCell.Offset(0, 0).Select
ActiveCell.FormulaR1C1 = ActiveCell.Value
Range(Selection, Selection.End(xlToRight)).Select


Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Range("a2").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

Exit Sub


End Sub