Match data in 2 columns and give value from 3rd column

Copper Contributor

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

LisaPriest_0-1649649347336.png

 

3 Replies

@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.

@LisaPriest 

=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. 

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 :D Appreciate your time!