Two-way lookup

Advanced

Imagine your sales team keeps pricing in a matrix: products down the side, regions across the top. To build quotes quickly, you need a two-way lookup that finds the intersection of both.

In this sheet, the price matrix is in A4:E9:

  • Products are listed in A5:A9
  • Regions are listed in B4:E4
  • Prices are in B5:E9

Your task

Complete the quote requests in A12:F15:

  1. In E13, return the correct Unit price based on the Product in B13 and the Region in C13.
  2. Fill the formula down through E15.
  3. In F13, calculate Monthly total as Units × Unit price, then fill down through F15.

Use an INDEX + MATCH + MATCH setup (or a nested XLOOKUP) and lock the matrix ranges so the formulas copy cleanly.

Need some help?

Hint 1

A two-way lookup is usually MATCH for the product row and MATCH for the region column, then INDEX returns the intersecting price.

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.

Related function(s)
INDEXMATCHXLOOKUPXMATCHVLOOKUP
More exercises
INDEX-MATCH left lookupIntermediate
XLOOKUP left lookupIntermediate
Look up a manager with INDEX+MATCHIntermediate
Return multiple columnsAdvanced
Back to exercises overview

Exercise