How to multiply quantities by prices from a lookup table in Excel
Excel formula
=SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6))Look up every price at once and multiply inside SUMPRODUCT: =SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6)).
- Result on the example
- $45.60 in
H1 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in H1. Change any input and the result updates, or edit the formula to experiment.
How it works
Given the whole list of items, XLOOKUP returns five prices, one per order line, in the same order as the quantities. SUMPRODUCT multiplies each quantity by its price and adds them, so there is no need for a price column next to the order.
=SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6))Result45.6
B2:B65 rows × 1 col: 3, 2, 1, 2, …A range: 5 cells in one column.
A2:A65 rows × 1 col: "Latte", "Muffin", "Espresso", "Cappuccino", …A range: 5 cells in one column.
D2:D65 rows × 1 col: "Espresso", "Latte", "Cappuccino", "Muffin", …A range: 5 cells in one column.
E2:E65 rows × 1 col: $3.25, $4.75, $4.50, $3.95, …A range: 5 cells in one column.
XLOOKUP(A2:A6,D2:D6,E2:E6){5 rows × 1 col} 4.75, 3.95, 3.25, 4.5, …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
A2:A65 rows × 1 col: "Latte", "Muffin", "Espresso", "Cappuccino", … - lookup_array
D2:D65 rows × 1 col: "Espresso", "Latte", "Cappuccino", "Muffin", … - return_array
E2:E65 rows × 1 col: $3.25, $4.75, $4.50, $3.95, …
- lookup_value
SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6))45.6SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B65 rows × 1 col: 3, 2, 1, 2, … - array2
XLOOKUP(A2:A6,D2:D6,E2:E6){5 rows × 1 col} 4.75, 3.95, 3.25, 4.5, …
- array1
In Google Sheets
Google Sheets needs a different formula: =SUMPRODUCT(B2:B6,SUMIF(D2:D6,A2:A6,E2:E6)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(B2:B6,SUMIF(D2:D6,A2:A6,E2:E6)) | $45.60 | Works in every version and in Google Sheets: SUMIF with a list of items returns each item's price. |
Common mistakes
- An item missing from the price list makes XLOOKUP return #N/A and the whole total fails. Give it a fallback:
XLOOKUP(A2:A6,D2:D6,E2:E6,0). - The SUMIF version quietly prices a missing item at 0, and adds up the prices if an item is listed twice.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Multiply quantities by prices from a lookup table
H1, calculate the order total: each quantity times the item's price from the price list in D2:E6.Questions
How do I show each line total?
Use =B2*XLOOKUP(A2,$D$2:$D$6,$E$2:$E$6) filled down.
How do I apply a discount to the total?
Multiply the result: =SUMPRODUCT(…)*(1-G2) with the discount rate in G2.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.