# How to multiply a looked-up price by a quantity in Excel

Multiply the quantity by the lookup: `=B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)`, filled down.

**Excel:** `=B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Item | Qty | Line total |  | Item | Unit price |
| Espresso beans 1 kg | 3 |  |  | Espresso beans 1 kg | 27.9 |
| Oat milk 6-pack | 2 |  |  | Oat milk 6-pack | 18.2 |
| Paper cups (100) | 5 |  |  | Paper cups (100) | 9.5 |
| Green tea 100 bags | 1 |  |  | Green tea 100 bags | 9.25 |
| Espresso beans 1 kg | 2 |  |  | Cocoa powder 500 g | 12.4 |
| Cocoa powder 500 g | 4 |  |  | Vanilla syrup | 7.95 |

Formula in C2, filled to C7: `=B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)`

Result: $83.70; $36.40; $47.50; $9.25; $55.80; $49.60

## How it works

XLOOKUP returns the unit price for the item, $27.90 for espresso beans, and the formula multiplies it by the quantity in B2. A lookup is just a value inside a calculation, so you can multiply, add or compare it like any number.

## Other ways

- `=B2*VLOOKUP(A2,$E$2:$F$7,2,FALSE)`: The VLOOKUP version, for every Excel version.

## Common mistakes

- Lock the price list with `$`, or the ranges slide down as you fill and later items return #N/A.
- An item missing from the price list returns #N/A for that line. Use `XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,0)` only if a zero price is acceptable; otherwise fix the list.

## Questions

**How do I get the order total in one cell?**

Multiply the arrays and add: `=SUMPRODUCT(B2:B7,XLOOKUP(A2:A7,E2:E7,F2:F7))`.

**How do I apply a discount too?**

Multiply again: `=B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)*(1-$H$1)`, with the discount rate in H1.

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-lookup-by-quantity