Lookup FunctionsAdvanced

Two-Way Lookup

Perform a two-way lookup to find values at the intersection of a row and column.

Task:

You have a sales matrix with regions as rows and quarters as columns. Find the sales value for a specific region and quarter combination using a two-way lookup.

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of INDEX MATCH

0/3

The same formula in the other shapes it takes at work.

  1. Two-Way Lookup

    Perform a two-way lookup to find values at the intersection of a row and column.

    CoreAdvanced
  2. Two-way lookup with VLOOKUP and MATCH

    VLOOKUP finds the row; MATCH tells it which column.

    Advanced
  3. Two-way lookup: an approximate row and an exact column

    Weight band down the side, zone across the top — two different kinds of match.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

9 rows × 5 columns2 cells you fill in
ABCDE
1JanFebMarApr
2North15000160001700018000
3South12000130001400015000
4East14000150001600017000
5West11000120001300014000
6
7RegionMonthSales
8SouthMar
9EastFeb
What this exercise teachesMay contain the answer

Two-way lookups find values at the intersection of a specified row and column, useful for matrices like sales by region and time period.