Match 2 values
 

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

Match 2 values
 

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

Match 2 values
 

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

Match 2 values
 

Assume the following: Your Table 1 is in C6:E14. You second table is
in C20:D25. Enter the following formula in E20 and fill down to E25.

=SUMPRODUCT(--($C$6:$C$14=C20),--($D$6:$D$14=D20),$E$6:$E$14)

This will return the sum of the values in E6:E14 where the
corresponding value in C6:C14 is equal to the value in C20 and the
corresponding value in D6:D14 is equal to the value in D20.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Mon, 8 Jun 2009 06:16:01 -0700, 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

Match 2 values
 

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