# How to multiply quantities by prices from a lookup table in Excel

Look up every price at once and multiply inside SUMPRODUCT: `=SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6))`.

**Excel:** `=SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=SUMPRODUCT(B2:B6,SUMIF(D2:D6,A2:A6,E2:E6))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Item | Qty |  | Item | Price |  | Order total |
| Latte | 3 |  | Espresso | 3.25 |  |  |
| Muffin | 2 |  | Latte | 4.75 |  |  |
| Espresso | 1 |  | Cappuccino | 4.5 |  |  |
| Cappuccino | 2 |  | Muffin | 3.95 |  |  |
| Bagel | 4 |  | Bagel | 2.8 |  |  |

Formula in H1: `=SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6))`

Result: $45.60

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

## Other ways

- `=SUMPRODUCT(B2:B6,SUMIF(D2:D6,A2:A6,E2:E6))`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/multiply-quantities-by-prices-from-a-lookup-table