In the other workbook try the below

with
A2 = 101
B2 = 201
=SUMPRODUCT(--([Book1]Sheet1!$A$1:$A$50000=A2),--([Book1]Sheet1!$B$1:$B$50000=B2),--([Book1]Sheet1!$C$1:$C$50000))
(formula in one line)

If this post helps click Yes
---------------
Jacob Skaria


"Lupus" wrote:

Hi

The data are located in 2 worksbooks. Book1 and Book2. I need the data units
from Book1 inserted into Book2. Book1 contains about 50 000 rows of data.
Tried to add Book1! to the formula but excel came back with an error.

=SUMPRODUCT(--(book1!$A$1:$A$50000=D2),--(book1!$B$1:$B$100=E2),--($C$1:$C$100))

I inserted this into C2

Custno=A, Artno=B, Units=C in both workbooks

Hope this additional info is helpfull.

Eirik

"Jacob Skaria" wrote:

Suppose you have the below data in D:F in the same sheet where you have Table1

Custno Artno Units
101 201 x
101 203 x
102 201 x
102 203 x
103 201 x
103 203 x

replace x with the below formula in F2 and copy that down as required
=SUMPRODUCT(--($A$1:$A$100=D2),--($B$1:$B$100=E2),--($C$1:$C$100))

If this post helps click Yes
---------------
Jacob Skaria


"Lupus" wrote:

Hi

I have 1 table with data received from a customer. There I have Storenumber,
Articlenumber and units in stock. I wan't to match this data with a table
with a list of articlenumbers and how many units the need to reorder.

Table 1 looks like this
Custno Artno Units
101 201 3
101 202 2
101 203 9
102 201 1
102 202 2
102 204 5
103 205 3
103 201 5
103 202 3

Table 2 looks like this

Custno Artno Units
101 201
101 203
102 201
102 203
103 201
103 203


I want the result to look like this

Custno Artno Units
101 201 3
101 203 9
102 201 1
102 203 0
103 201 5
103 203 0

If there are no value of a spesific article I wan't 0 returned.

Is this possible?

Vlookup and match only generates errors

Eirik