Introduction
Modern Excel has evolved far beyond basic calculations and traditional worksheet formulas. With dynamic-array and advanced functions such as LET, XLOOKUP, LAMBDA, MAP, SCAN, BYROW, BYCOL, MAKEARRAY, TAKE, DROP, SORTBY, CHOOSECOLS, and XMATCH, you can build powerful solutions with fewer formulas and less manual work.
The examples are designed to show not only what each formula does, but also how the logic works and where the technique can be applied in real-world Excel projects. By the end, you will understand how modern Excel functions can work together to create dynamic, automated, and user-friendly reports without relying heavily on helper columns or repetitive formulas.
1. Dynamic
Sales Filter with LET + FILTER
Imagine that you have a sales database
containing orders from different regions. You want to create a report where the
user can select a region, and Excel automatically displays only the sales
records for that region. If the user changes the selection, the report updates
automatically. This is a useful technique for dynamic reports and interactive
dashboards.
Step 1: Create the sales data
Enter the headings in A4:D4: Order,
Region, Product, Sales. Then enter:
In G1, enter North. This cell will be
the region selector.
Step 2: Enter the formula
=LET(data,A5:D14,region,G1,FILTER(data,INDEX(data,,2)=region,"No
records found"))
Step 3: Understand the formula
LET creates names for parts of the
formula. Here, data represents A5:D14 and region represents G1. This makes the
formula easier to read. INDEX(data,,2) returns the second column, which is
Region. FILTER then returns only rows where the Region matches the selected
value.
For North, the result is:
Change G1 to South and the result automatically changes.
Practical uses
- Sales dashboards
- Regional reports
- Management reports
- Dynamic search panels
- Customer and product reports
- Interactive Excel dashboards
2.
Multi-Criteria Lookup with XLOOKUP
Suppose you have employee information
and want to find an employee's salary using two conditions at the same time:
Employee ID and Department. This is a multi-criteria lookup.
Step 1: Create the employee data
Enter the headings in A4:E4: Employee
ID, Employee, Department, Year, Salary. Then enter:
Enter E004 in H5 and IT in H6.
Step 2: Enter the formula
=XLOOKUP(1,(A5:A14=H5)*(C5:C14=H6),E5:E14,"Not Found")
The result is 95,000.
How the formula works
A5:A14=H5 checks the Employee ID.
C5:C14=H6 checks the Department. Multiplying the two conditions creates an AND
condition. Only the row where both conditions are TRUE produces 1. XLOOKUP
searches for 1 and returns the matching salary.
Practical uses
- Employee lookup
- Customer lookup
- Product lookup
- Invoice lookup
- Department-specific records
- Multi-condition dashboards
3. Progressive
Tiered Commission with MAP + LAMBDA
Suppose a company pays salespeople using
a progressive commission system. The first 50,000 receives 2%, the next 50,000
receives 5%, the next 50,000 receives 8%, and any amount above 150,000 receives
10%. Each portion must be calculated using its own rate.
Step 1: Create the salesperson data
In A4:B14, enter the headings
Salesperson and Sales, followed by:
Step 2: Create the commission structure
In E4:F8, enter the headings Threshold
and Rate, followed by:
Step 3: Enter the formula
=SUM(MAP($E$5:$E$8,LAMBDA(tier,MAX(0,MIN(B5-tier,IFERROR(INDEX($E$5:$E$8,MATCH(tier,$E$5:$E$8,0)+1)-tier,B5-tier)))))*$F$5:$F$8)
Copy the formula down to C14.
Results
Understand Emma's calculation
Emma has sales of 220,000. The formula
divides the sales into the correct tiers:
- 50,000 × 2% = 1,000
- 50,000 × 5% = 2,500
- 50,000 × 8% = 4,000
- 70,000 × 10% = 7,000
Total commission = 14,500.
Why this formula is advanced
MAP processes each tier independently.
LAMBDA defines the calculation for each tier. MIN limits the amount assigned to
a tier. MAX prevents negative values. INDEX and MATCH identify the next
threshold.
Practical uses
- Sales commissions
- Tax brackets
- Employee bonuses
- Progressive discounts
- Pricing tiers
- Performance incentives
4. Running
Sales Total with SCAN
Suppose a company records daily sales
and wants to see cumulative sales after every day. SCAN can calculate the
complete running total with one formula.
Step 1: Enter the data
In A4:B14, enter:
Step 2: Enter the formula
=SCAN(0,B5:B14,LAMBDA(total,value,total+value))
Result
SCAN starts with 0, adds the first
value, then adds the next value to the previous result. Unlike a normal SUM,
SCAN returns every intermediate result.
Practical uses
- Running sales
- Cumulative revenue
- Cumulative expenses
- Account balances
- Inventory balances
- Project progress
5. Sequential
Inventory Balance with SCAN
A warehouse receives and sells stock
throughout the day. You want Excel to calculate the inventory balance after
every transaction. Stock-in increases inventory, while a sale decreases
inventory.
Step 1: Enter the data
In A4:C14, enter:
Step 2: Enter the formula
=SCAN(0,C5:C14*IF(B5:B14="Sale",-1,1),LAMBDA(balance,change,balance+change))
Result
IF(B5:B14="Sale",-1,1) changes
sales into negative quantities. For example, Sale 15 becomes -15, while Stock
In 50 remains +50. SCAN then continuously adds the changes.
6. Department
KPI Analysis with BYROW
A company tracks department performance
across four quarters and wants one average KPI for each department. BYROW can
apply the same calculation to every row.
Step 1: Enter the data
In A4:E14, enter:
Step 2: Enter the formula
=BYROW(B5:E14,LAMBDA(row,AVERAGE(row)))
Result
BYROW takes one row at a time. LAMBDA(row,AVERAGE(row))
tells Excel to calculate the average of the current row. Excel automatically
repeats this for every department.
7. Quarterly
Analysis with BYCOL
Now suppose management wants the average
KPI for each quarter rather than each department. BYCOL performs one
calculation for each column.
Formula
=BYCOL(B5:E14,LAMBDA(col,AVERAGE(col)))
The results are 83.8 for Q1, 85.9 for
Q2, 85.8 for Q3, and 89.1 for Q4.
BYROW versus BYCOL
BYROW works row by row:
=BYROW(B5:E14,LAMBDA(row,AVERAGE(row)))
BYCOL works column by column:
=BYCOL(B5:E14,LAMBDA(col,AVERAGE(col)))
A simple way to remember the difference
is: BYROW gives one result for each row, while BYCOL gives one result for each
column.
Practical uses
- Monthly averages
- Quarterly analysis
- Yearly analysis
- Regional comparisons
- Product comparisons
- Performance analysis
8. Dynamic KPI
Matrix with MAKEARRAY
Suppose a company has employee targets
and management wants a new target that is automatically 10% higher. MAKEARRAY
can generate the complete output dynamically.
Step 1: Enter the data
In A4:B14, enter:
Step 2: Enter the formula
=MAKEARRAY(10,2,LAMBDA(r,c,IF(c=1,INDEX(A5:A14,r),INDEX(B5:B14,r)*1.1)))
Result
MAKEARRAY(10,2) creates a 10-row by
2-column dynamic array. r represents the current row and c represents the
current column. When c equals 1, Excel returns the employee name. Otherwise it
returns the original target multiplied by 1.1.
Practical uses
- Target planning
- Forecast models
- Budget scenarios
- KPI matrices
- Automated planning
- Scenario analysis
9. Dynamic Top
5 Sales with TAKE + SORTBY
A sales manager wants a dynamic report
showing the five highest-performing salespeople. The ranking should update
automatically when sales values change.
Step 1: Enter the data
In A4:B14, enter:
Step 2: Enter the formula
=TAKE(SORTBY(A5:B14,B5:B14,-1),5)
Result
SORTBY first sorts the data by Sales
from largest to smallest. TAKE then returns the first five rows. Because the
result is dynamic, the Top 5 list changes automatically when the source data
changes.
Practical uses
- Top 5 salespeople
- Top 10 customers
- Best-selling products
- Highest-performing branches
- Employee rankings
- Dashboard leaderboards
10. Dynamic
Report Cleaning with DROP
Imported reports often contain extra
rows, such as a report title at the beginning and a total row at the end. DROP
can remove those unwanted rows while keeping the actual records.
Step 1: Enter the data
In A4:C15, enter:
Step 2: Enter the formula
=DROP(DROP(A5:C15,1),-1)
The first DROP removes the first row.
The second DROP removes the last row. The final output therefore contains only
the product records.
Practical uses
- Accounting exports
- ERP reports
- CSV imports
- System-generated reports
- Sales reports
- Financial reports
- Imported datasets
11. Dynamic
Column Selection with CHOOSECOLS + XMATCH
Imagine you are creating an Excel
reporting system. Your source contains ID, Region, Product, Sales, and Profit,
but different users want different columns. Instead of creating several
reports, let users select the columns they want.
Step 1: Create the data
In A4:E14, enter:
Step 2: Create the column selectors
In H1:J1, enter Region, Product, Profit.
Step 3: Enter the formula
=CHOOSECOLS(A4:E14,XMATCH(H1:J1,A4:E4))
Result
Step 4: Change the selected columns
Change H1:J1 to ID, Sales, Profit. The report automatically changes.
XMATCH finds the positions of the selected headings. CHOOSECOLS then returns those columns. This creates a flexible report builder controlled by the user's selections.
Practical uses
- Interactive dashboards
- Dynamic report builders
- Management reports
- User-controlled reports
- Custom analysis tools
- Flexible data views
Complete
Formula Reference
1. Dynamic Sales Filter
=LET(data,A5:D14,region,G1,FILTER(data,INDEX(data,,2)=region,"No
records found"))
2. Multi-Criteria Lookup
=XLOOKUP(1,(A5:A14=H5)*(C5:C14=H6),E5:E14,"Not Found")
3. Progressive Commission
=SUM(MAP($E$5:$E$8,LAMBDA(tier,MAX(0,MIN(B5-tier,IFERROR(INDEX($E$5:$E$8,MATCH(tier,$E$5:$E$8,0)+1)-tier,B5-tier)))))*$F$5:$F$8)
4. Running Sales
=SCAN(0,B5:B14,LAMBDA(total,value,total+value))
5. Inventory Balance
=SCAN(0,C5:C14*IF(B5:B14="Sale",-1,1),LAMBDA(balance,change,balance+change))
6. Department KPI
=BYROW(B5:E14,LAMBDA(row,AVERAGE(row)))
7. Quarterly KPI
=BYCOL(B5:E14,LAMBDA(col,AVERAGE(col)))
8. Dynamic KPI Matrix
=MAKEARRAY(10,2,LAMBDA(r,c,IF(c=1,INDEX(A5:A14,r),INDEX(B5:B14,r)*1.1)))
9. Top 5 Sales
=TAKE(SORTBY(A5:B14,B5:B14,-1),5)
10. Remove Header/Footer
=DROP(DROP(A5:C15,1),-1)
11. Dynamic Columns
=CHOOSECOLS(A4:E14,XMATCH(H1:J1,A4:E4))
How These
Functions Work Together
The real power of modern Excel comes from combining functions.
LET + FILTER is excellent for dynamic filtering. You
can use it to build a dashboard where a user selects a region or category and
the complete report updates automatically.
XLOOKUP + Boolean logic is useful when a
lookup needs multiple conditions. Instead of creating helper columns, the
conditions can be combined directly inside the lookup formula.
MAP + LAMBDA is useful when you have a
repeated calculation but the calculation is more complicated than a normal
Excel function. The progressive commission example demonstrates this approach.
SCAN + LAMBDA is useful when every
result depends on the previous result. This makes it ideal for running totals,
balances, inventory, and cumulative calculations.
BYROW and BYCOL are useful when working
with a matrix of values. BYROW calculates one result per row, while BYCOL
calculates one result per column.
MAKEARRAY is useful when you want Excel
to generate an entire dynamic array based on a custom rule.
TAKE + SORTBY is a simple but powerful
combination for creating Top 5, Top 10, or other ranking reports.
DROP is useful when imported data
contains unnecessary rows that need to be removed dynamically.
CHOOSECOLS + XMATCH is particularly
useful for interactive reports because users can decide which columns they want
to see.
0 Comments