How to multiply a looked-up price by a quantity in Excel
Formula (Excel and Google Sheets)
=B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)Multiply the quantity by the lookup: =B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7), filled down.
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
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.
=B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)Result83.7
B23The value in B2.
A2"Espresso beans 1 kg"The value in A2.
$E$2:$E$76 rows × 1 col: "Espresso beans 1 kg", "Oat milk 6-pack", "Paper cups (100)", "Green tea 100 bags", …A range: 6 cells in one column.
$F$2:$F$76 rows × 1 col: $27.90, $18.20, $9.50, $9.25, …A range: 6 cells in one column.
XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)27.9XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
A2"Espresso beans 1 kg" - lookup_array
$E$2:$E$76 rows × 1 col: "Espresso beans 1 kg", "Oat milk 6-pack", "Paper cups (100)", "Green tea 100 bags", … - return_array
$F$2:$F$76 rows × 1 col: $27.90, $18.20, $9.50, $9.25, …
- lookup_value
B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)83.7The * operator multiplies.
In Google Sheets
The same formula works in Google Sheets: =B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =B2*VLOOKUP(A2,$E$2:$F$7,2,FALSE) | $83.70, $36.40, $47.50… | 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.
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 a looked-up price by a quantity
C2, calculate the line total: the quantity times the unit price from the price list in E2:F7. Fill down to C7.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.
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.