# Return an item's price from a list by matching its item number

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:

H I J
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:

B C D E
1 Request Price Quantity Total
2 C1 100 1 100
3 C1 100 2 200

Spreadsheet Screenshot

## 1 Answer

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

``={"Price"; ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, H:J, 3, 0)))}``

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

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