I'm struggling with a SUMPRODUCT

I have two worksheets (WS1 and WS2) both with the following columns:
'Material Number' (Column A) and 'Site' (Column B). They each have around
9000 rows. In WS2 there is another value, 'Group' (Column C)

If the a Material and Site combination from WS1 are found in WS2, return the
Group in WS2:Column C to Column C in WS1.

Any ideas?

=SUMPRODUCT(--(WS2!$A$2:$A$10000=A2),--(WS2!$B$2:$B$10000=B2),--(WS2!$C$2:$C$10000))

If you merely select A:A, it chockes. Must be a range.

"D Zandveld" wrote:

I'm struggling with a SUMPRODUCT

I have two worksheets (WS1 and WS2) both with the following columns:
'Material Number' (Column A) and 'Site' (Column B). They each have around
9000 rows. In WS2 there is another value, 'Group' (Column C)

If the a Material and Site combination from WS1 are found in WS2, return the
Group in WS2:Column C to Column C in WS1.

Any ideas?

Thanks Sean, but i'm just getting '0'.

There are definitely ones to find.

Any other ideas, cell formatting etc. to consider?

"Sean Timmons" wrote:

=SUMPRODUCT(--(WS2!$A$2:$A$10000=A2),--(WS2!$B$2:$B$10000=B2),--(WS2!$C$2:$C$10000))

If you merely select A:A, it chockes. Must be a range.

"D Zandveld" wrote:

I'm struggling with a SUMPRODUCT

I have two worksheets (WS1 and WS2) both with the following columns:
'Material Number' (Column A) and 'Site' (Column B). They each have around
9000 rows. In WS2 there is another value, 'Group' (Column C)

If the a Material and Site combination from WS1 are found in WS2, return the
Group in WS2:Column C to Column C in WS1.

Any ideas?

Any other ideas, cell formatting etc. to consider?

If you're handling mixed data, try the array-entered* index/match option

In WS1, in C2, array-entered*:
=INDEX(WS2!$C$2:$C$10000,MATCH(1,(WS2!$A$2:$A$1000 0=A2)*(TRIM(WS2!$B$2:$B$10000)=TRIM(B2)),0))
*Press CTRL+SHIFT+ENTER

Above also uses TRIM for more robust matching to shake out the possible
extraneous white spaces in either/both the source col B in WS2 and the lookup
value in B2 which would otherwise throw apparent matches off
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---

Any other ideas, cell formatting etc. to consider?

Another play*, normal ENTER will do (no need to array-enter):
=INDEX(WS2!$C$2:$C$10000,MATCH(1,INDEX((WS2!$A$2:$ A$10000=A2)*(TRIM(WS2!$B$2:$B$10000)=TRIM(B2)),),0 ))

*idea inspired by a couple of Teethless Mama's recent postings
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---