cross-sheet lookup

[impulse24]

Hi,

I can't figure out how to do this.

I have a sinle excel workbook with 2 spreadsheets. The first shee
contains the following

orderid ttqty ssqty
1234
5678
9101


The second sheet contains:
orderid sku qty
1234 ttqty 2
1234 ssqty 1
4567 ssqty 3
4567 ttqty 1

In the first sheet I need the end result to be:

orderid ttqty ssqty
1234 2 1
4567 1 3

Basically I am wanting to lookup the sku value from the 2nd sheet, an
place the qty into the first. I am thinking I have to create a range i
the 2nd sheet, and use the vlookup function, but am not entirely sur
how. Please help

 

[Don Guillett]

=sumproduct((sheet2!a2:a22=a2)*(sheet2!b2:b22=$b$1)*sheet2!c2:c22)

 

[Frank Kabel]

Hi
in addition to Don's formula you may consider using pivot tables to
create such a report. Have a look at
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html