Solving Real Lookup Problems
Swipe to show menu
Exact matches are useful for relational models, but many business systems rely on ranges and thresholds instead of fixed values. In this chapter, use approximate matching to build tier-based pricing logic and dynamic discount calculations.
Approximate Match with XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], -1)
lookup_value: value being evaluated;lookup_array: threshold values;return_array: returned result;-1: returns the largest value less than or equal to the lookup value.
Approximate matching requires the lookup array to be sorted in ascending order.
Tier-Based Logic
Tier tables define minimum thresholds instead of exact matches.
Example:
| Min_Units | Discount_Rate |
|---|---|
| 1 | 0% |
| 5 | 5% |
| 10 | 10% |
| 20 | 15% |
Excel evaluates where the Units value fits inside the threshold structure and returns the corresponding discount rate.
Create a new worksheet named:
Pricing_Tiers
Add the following dataset:
Min_Units;Discount_Rate
1;0%
5;5%
10;10%
20;15%
Convert the dataset into an Excel Table.
Confirm that Min_Units is sorted in ascending order.
Inside Sales_Data, create a new column named:
Discount_Rate
Inside the first row, type:
=XLOOKUP([@Units],Pricing_Tiers[Min_Units],Pricing_Tiers[Discount_Rate],0,-1)
[@Units]: current row Units value;Pricing_Tiers[Min_Units]: threshold column;Pricing_Tiers[Discount_Rate]: returned discount value;0: fallback value;-1: approximate match mode.
Format the results as percentages.
Create a new column named:
Discounted_Revenue
Inside the first row, type:
=[@Revenue]*(1-[@Discount_Rate])
[@Revenue]: original Revenue value;[@Discount_Rate]: applied discount percentage.
The calculation now adjusts dynamically based on unit volume.
Modify the values inside Pricing_Tiers.
Confirm that all dependent calculations update automatically.
Replace the previous top product formula with:
=XLOOKUP(MAX(Sales_Data[Discounted_Revenue]),Sales_Data[Discounted_Revenue],Sales_Data[Product],"Not found")
The lookup now evaluates discounted performance instead of raw Revenue.
Inside the Summary sheet, create an input section for:
Order_ID
Inside the lookup result cell, type:
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Customer_ID],"Not found")
M2: Order_ID input;Sales_Data[Order_ID]: lookup column;Sales_Data[Customer_ID]: returned value.
Inside another result cell, type:
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Product],"Not found")
The formulas now retrieve transaction details dynamically from the selected Order_ID.
Change the value inside M2.
Confirm that:
- Customer values update automatically;
- Product values update automatically;
- The formulas always return the matching transaction.
1. What does match_mode = -1 do in XLOOKUP?
2. Why must the lookup array be sorted when using approximate match?
3. What makes XLOOKUP suitable for reverse lookups?
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
