Two-way lookup
Advanced Sales OpsSales and pricing teams often store rates in a matrix with one dimension by product and the other by region. A two-way lookup returns the value at the exact row/column intersection.
In this sheet, use the price matrix in A4:E9 to complete the quote rows in A13:F15.
Your objective:
- In E13:E15, return unit price from Product (column B) and Region (column C) using a two-way lookup.
- In F13:F15, calculate monthly total as Units × Unit price.
- Keep ranges anchored so formulas fill down correctly.
Need some help?
Hint 1
A possible approach: INDEX with two MATCH functions (row match for product, column match for region).
Hint 2
Lock the matrix ranges ($) so they don’t shift when you fill from row 13 to 15.
Hint 3
For totals, multiply Units by the looked-up Unit price in the same row.
Two-way lookup
Advanced Sales OpsSales and pricing teams often store rates in a matrix with one dimension by product and the other by region. A two-way lookup returns the value at the exact row/column intersection.
In this sheet, use the price matrix in A4:E9 to complete the quote rows in A13:F15.
Your objective:
- In E13:E15, return unit price from Product (column B) and Region (column C) using a two-way lookup.
- In F13:F15, calculate monthly total as Units × Unit price.
- Keep ranges anchored so formulas fill down correctly.
Need some help?
Hint 1
A possible approach: INDEX with two MATCH functions (row match for product, column match for region).
Hint 2
Lock the matrix ranges ($) so they don’t shift when you fill from row 13 to 15.
Hint 3
For totals, multiply Units by the looked-up Unit price in the same row.