I have a spreadsheet with a list of the items I sell.

For each item (1 per row), I have the following columns:

  1. Item Code in Column H
  2. Item (name) in Column I
  3. Price Per Pack in Column J

Example Data:

1 Item Code Item Per Pack
2 C1 Choco Chip Cookies 100
3 C2 Jolly Cookies 100
4 C3 Cookies and Cream 100
5 M1 Choco Mallows 85
6 M2 Filled Mallows 180
7 N1 Pistachios 110
8 N4 Cashews 85
9 N5 Cornick 25
10 G1 Gummy Worms 25

In the same sheet, I am trying to create a formula in Column C that returns the price that matches the Item Code chosen from the dropdown in Column B (Request).

In Column D I enter the Quantity and in Column E I can calculate the Total once I have the price.

I succeeded using the IF function for item "C1" but it only works for one item: =IF(B2="C1",100,)

How do I return the Price for an item using the Item Code chosen in Column B?

Example Results:

1 Request Price Quantity Total
2 C1 100 1 100
3 C1 100 2 200

Spreadsheet Screenshot
Spreadsheet Screenshot


1 Answer 1

  • delete everything in B column and paste this into B1 cell:


  • delete everything in E column and paste this into E1 cell:

={"Total"; ARRAYFORMULA(IF(C2:C<>"", C2:C*D2:D, ))}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.