Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Learn Dynamic and Parameter-Driven Lookups | Advanced Lookup Systems and Relational Modeling
Excel Formulas

Dynamic and Parameter-Driven Lookups

Swipe to show menu

The workbook already supports relational lookups and dynamic reporting. In this chapter, build category-level summaries and introduce parameter-driven logic that changes calculations dynamically based on user-selected scenarios.

SUMPRODUCT Structure

=SUMPRODUCT(array1 * array2 * ...)
  • array1: first calculation array;
  • array2: second calculation array;
  • TRUE: converted into 1;
  • FALSE: converted into 0.

This allows logical conditions and aggregation to happen inside a single formula.

INDIRECT Structure

=INDIRECT(ref_text, [a1])
  • ref_text: text converted into a live reference;
  • [a1]: optional reference style argument.

INDIRECT allows formulas to switch references dynamically based on cell values.

Step 1 Create the Category Summary
expand arrow

Inside the Summary sheet, add the following headers:

Category
Total_Revenue
Total_Cost
Total_Profit
Step 2 Generate the Category List
expand arrow

Inside A10, type:

=UNIQUE(Products[Category])

The category list now expands automatically as new categories are added.

Step 3 Calculate Revenue by Category
expand arrow

Inside B10, type:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*Sales_Data[Revenue])
  • XLOOKUP(...): retrieves category values for every product;
  • =A10: checks whether the category matches;
  • Sales_Data[Revenue]: values being aggregated.

Fill the formula down the column.

Step 4 Calculate Cost by Category
expand arrow

Inside C10, type:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*XLOOKUP(Sales_Data[Product],Products[Product],Products[Cost],0)*Sales_Data[Units])

The formula dynamically calculates total cost by category.

Step 5 Calculate Profit
expand arrow

Inside D10, type:

=B10-C10

Fill the formula down and format all values appropriately.

Step 6 Create the Scenario Selector
expand arrow

Inside the Summary sheet, create a cell for:

Active Pricing Scenario

Apply Data Validation using the following options:

Pricing_Tiers
Pricing_Tiers_Promo
Step 7 Build the Dynamic Discount Lookup
expand arrow

Inside Sales_Data, replace the previous discount formula with:

=XLOOKUP([@Units],INDIRECT(Summary!$F$9&"[Min_Units]"),INDIRECT(Summary!$F$9&"[Discount_Rate]"),0,-1)
  • Summary!$F$9: selected scenario table;
  • INDIRECT(...): converts text into active table references;
  • -1: approximate match mode.

The lookup now switches dynamically between pricing scenarios.

Step 8 Test Scenario Switching
expand arrow

Change the selected value inside the scenario dropdown.

Confirm that:

  • Discount_Rate updates automatically;
  • Discounted_Revenue updates automatically;
  • All dependent calculations react to the selected pricing model.

1. What is the role of SUMPRODUCT in this lesson?

2. Why is INDIRECT used in parameter-driven models?

3. What is the main benefit of using UNIQUE with SUMPRODUCT in summary tables?

question mark

What is the role of SUMPRODUCT in this lesson?

Select the correct answer

question mark

Why is INDIRECT used in parameter-driven models?

Select the correct answer

question mark

What is the main benefit of using UNIQUE with SUMPRODUCT in summary tables?

Select the correct answer

Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 3. Chapter 5

Ask AI

expand

Ask AI

ChatGPT

Ask anything or try one of the suggested questions to begin our chat

Section 3. Chapter 5
some-alt