Extract records

**ScottXe** · 01-23-2013, 08:26 PM

I have an excel file containing the models details. I have another excel file containing the orders of models. Firstly I need to sum up the orders of each model. Secondly I need to move some details of each model to the corresponding models of the order file for further calculation. This work will be doing quarterly. Can someone give me the idea how to accomplish it. Thanks.

**vlady** · 01-23-2013, 08:52 PM

you can use the sumif/s function in summing each orders per model using later xl..or the sumproduct for xl 2003
you can use the vlookup or index/match to "move" some details.

**ScottXe** · 01-23-2013, 10:18 PM

Thanks for advice. I am unfamilar with using the vlookup or index/match to "move" some details. Can you provide me the pointer to use it. Thanks!

**vlady** · 01-23-2013, 10:35 PM

look at the sample file..

vlookupindex2.xls

**ScottXe** · 01-26-2013, 11:57 PM

Originally Posted by vlady

look at the sample file..

Attachment 209045

Thanks for your example. I understand how to apply it. It seems to copy only one column of VLOOKUP table and I need to copy multiple columns of the matched records. Does VLOOKUP function support it or else function is required?

**vlady** · 01-27-2013, 07:32 PM

in this formula

=VLOOKUP($J4,$A$3:$D$8,COLUMN(B1),TRUE) -> when fill to the right will increment column allowing for multiple columns extraction of records.
try it in the sample file. also fill down.

Take note on the $ signs in the formula

explanation
$J4 ->column J will not change when drag(fill handle)
J$4 ->column J change when drag to right(fill handle)
$J$4 -> this reference will not change when dragged(fill handle)