# conditional multiplication

• 05-15-2019, 05:43 PM
jsm250
conditional multiplication
On sheet 1, I have the "product" and "units sold". On sheet 2 I have "product" and "product cost". For every row on sheet 1, I need to calculate "cost of goods" by comparing the product on sheet 1 to product on sheet 2 and matching the price of the product and multiplying with the 'units sold'. Please see table below. Highly appreciate any help offered to solve this conditional multiplication. Thanks

Sheet 1
"Product" "Units Sold" "Cost of Goods"
Item 1 ----- 500
Item 2 ----- 409
Item 1 ----- 678
Item 4 ----- 879
Item 2 ----- 98
Item 6 ----- 23
Item 9 ----- 665
Item 8 ----- 76
Item 1 ----- 90
Item 4 ----- 532

Sheet 2
Product Product Cost
Item 1 ----- \$4.00
Item 2 ----- \$5.00
Item 3 ----- \$3.00
Item 4 ----- \$2.00
Item 5 ----- \$1.00
Item 6 ----- \$8.00
Item 7 ----- \$6.00
Item 8 ----- \$9.00
Item 9 ----- \$34.00
Item 10 ----- \$56.00
• 05-15-2019, 06:02 PM
TMS
Re: conditional multiplication
Use VLOOKUP to get the relevant cost and multiply by the quantity.
• 05-15-2019, 06:41 PM
jsm250
Re: conditional multiplication
Thank you. it works
• 05-15-2019, 06:44 PM
TMS
Re: conditional multiplication
Needs to refer to two columns to get the second one.

Formula:
`=VLOOKUP(E2,'Cost of Goods'!A2:B10,2,false)`
• 05-15-2019, 06:45 PM
jsm250
Re: conditional multiplication
Awesome, thank you
• 05-15-2019, 06:46 PM
TMS
Re: conditional multiplication
And you need to make the range absolute.

Formula:
`=VLOOKUP(\$E2,'Cost of Goods'!\$A\$2:\$B\$10,2,false)`
• 05-15-2019, 06:48 PM
TMS
Re: conditional multiplication
You're welcome. :)

New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit