Search Function

[cra88]

It has been a while since I worked with Excel and I have gone blank. Can
someone help me out please. I have 2 tabs, one with a list 453 model numbers
and the second with the entire 8000 models with the quantity on hand. I want
to create a list with the 453 models an the first tab and the quantity on
hand from the second tab.

Thanks

 

[T. Valko]

Try this...

Sheet1 A1:A453 = model numbers

Sheet2
A1:A8000 = model numbers
B2:B8000 = qunatities

Enter this formula on Sheet1 in cell B1:

=SUMIF(Sheet2!A$1:A$8000,A1,Sheet2!B$1:B$8000)

Copy down to B453.

Tip on quick copying...

Enter the formula in B1

With B1 still selected, double click the fill handle. The fill handle is
that little black square at the bottom right corner of the selected cell.
When you hover your mouse over the fill handle the cursor will change from a
"fat" cross to a "skinny" cross. When you get the "skinny" cross, double
click and the formulas will be copied down the column to the end of the
range.

 

[Eduardo]

Hi,
Let' say you have the 453 models in sheet 1 starting cell A2, the 8000
models are in sheet 2 column A and what is on hand in column B

so in cell B2 sheet 1 enter

=sumproduct(--(sheet2!$A$2:$A$9000=A2),sheet2!$B$2:$B$9000)

if you are using excel 2007 enter

=sumproduct(--(sheet2!A:A=A2),sheet2!B:B)