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.
New to this pattern? See INDEX MATCH explained step by step, from the position MATCH finds to the value INDEX returns.
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.
New to this pattern? See INDEX MATCH explained step by step, from the position MATCH finds to the value INDEX returns.
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.