Returning cell value if 2 criteria are met

  • Thread starter Thread starter D Zandveld
  • Start date Start date
D

D Zandveld

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?
 
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$10000=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
---
 
Back
Top