Forum Discussion
Match data in 2 columns and give value from 3rd column
Hello,
I have a pricing spreadsheet from our supplier, which gives their part numbers in column A. But many parts are available in different colours, so they have used a "Colour Code" entry in column C. (This is a numerical code that matches to the different colours eg 10=white.) Typically each colour is a different price for each part. They then have duplicate entries for each part number, with a different colour code for each row.
I'm creating a separate spreadsheet for only the parts we stock. I need to be able to match the supplier's part number in column A then also match the colour code. In the simplified example attached we only stock black jeans (colour code 11), so I need the price for Part Number 1308950 colour code 11, but not the other colours.
I hope that makes sense. Any input would be appreciated!
Lisa
Apologies that I haven't attached the sample as a file, I can't find the attachment button
3 Replies
Thanks very much OliverScheurich and Riny_van_Eekelen - I ran into trouble because there were also duplicate entries, so their prices were being added together to give the price. But I've worked out how to remove them and now it works 😄 Appreciate your time!
=SUMPRODUCT(($A$3:$A$8=H3)*($B$3:$B$8=I3)*($C$3:$C$8=J3)*$D$3:$D$8)An alternative could be above formula.
LisaPriest In J3, try this:
=SUMIFS($D$3:$D$100,$A$3:$A$100,H3,$C$3:$C$100,I3)Change the sum range (in column D) and the criteria ranges (columns A and C) to ones that correspond with your real schedule. Have attached a mock-up based on the screenshot.
