I’ve been doing this finance thing for long enough to remember when VLOOKUP felt like black magic. Now we’ve got dynamic arrays, XLOOKUP, and LAMBDA functions sitting in Excel — and half the finance professionals I meet are still hammering away with nested IFs and manual consolidations.
The gap between what Excel can do and what most finance teams actually use is wider than it should be. So here’s a practical guide to the functions that should be in your daily toolkit.
XLOOKUP: The VLOOKUP Killer
If you’re still using VLOOKUP, stop. XLOOKUP does everything VLOOKUP does, but better, and it’s been in Excel since 2019.
The syntax is cleaner:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Why it matters:
- You can look left (VLOOKUP couldn’t)
- You can search from the bottom up
- You set your own error message instead of getting #N/A
- It’s faster on large datasets
Real use case: matching invoice numbers across systems where the reference might be in any column. With VLOOKUP, you’d need helper columns or multiple attempts. XLOOKUP just works.
FILTER: Stop Copy-Pasting Subsets
FILTER is a dynamic array function that pulls out rows matching your criteria. The results update automatically when your source data changes.
=FILTER(array, include, [if_empty])
I use this constantly for management accounts. Instead of filtering and copying tabs for each department, I have one data table and FILTER formulas that update live. Change a transaction code in the source? Every departmental view updates instantly.
You can stack conditions too:
=FILTER(A2:D100, (C2:C100="Sales") * (D2:D100>10000))
That pulls all Sales transactions over £10,000. The asterisk acts as AND logic.
LET: Name Your Calculations
LET lets you define variables inside a formula. The benefit becomes clear once you’ve debugged one too many nested-parentheses nightmares.
=LET(
revenue, SUM(B2:B50),
costs, SUM(C2:C50),
margin, (revenue - costs) / revenue,
margin
)
The last argument is what the formula returns. Everything before that is just naming pieces for reuse.
Benefits:
- No more repeating complex calculations
- Formulas you can actually read six months later
- Faster performance (Excel calculates each piece once, not every time it appears)
I use LET for variance analysis. Name your actuals, name your budget, name your variance calc, then format it. The formula is ten times easier to debug than a wall of nested parentheses.
SORTBY: Control Your Data Order
SORTBY sorts one range based on another range’s values.
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
Practical example: You have a transaction list and want it sorted by date descending, then by amount descending within each date.
=SORTBY(A2:D100, B2:B100, -1, D2:D100, -1)
The -1 means descending. 1 (or omitted) means ascending.
This is cleaner than Excel’s Sort feature because it doesn’t touch your source data. You can have multiple sorted views from one dataset.
SEQUENCE: Generate Number Series
SEQUENCE creates arrays of sequential numbers. On its own it’s not exciting, but combine it with other functions and it gets useful.
=SEQUENCE(rows, [columns], [start], [step])
Use case: generating fiscal periods.
=TEXT(DATE(2024,SEQUENCE(12),1),"MMM-YY")
That gives you Jan-24 through Dec-24 in one formula. Helpful for building forecast templates where you want month headers to auto-populate.
LAMBDA: Build Your Own Functions
LAMBDA lets you create custom functions without VBA. You define parameters, write the logic, and save it as a named formula.
Here’s a margin calculation saved as a custom function:
=LAMBDA(revenue, cost, (revenue - cost) / revenue)
Save that with a name like “MARGIN” and you can use it anywhere:
=MARGIN(B2, C2)
For repetitive finance calculations (EBITDA, working capital, DSO), LAMBDA means you write the logic once and use it everywhere. Update the LAMBDA definition and every formula using it updates automatically.
Power Query: Not a Function But You Need It
Power Query deserves its own article, but if you’re doing any data consolidation, learn this tool. It sits in the Data tab under “Get Data.”
What it does:
- Connects to multiple files/sources
- Cleans and transforms data
- Refreshes with one click
The killer feature: you record the transformation steps once, then Excel repeats them on refresh. No more monthly “open 12 files, copy columns, paste, format, fix” routines.
I’ve seen month-end reporting cut from four hours to fifteen minutes using Power Query. The learning curve is real, but the return is immediate.
Dynamic Arrays: The Foundation
Most of these functions rely on dynamic array behavior — formulas that return multiple values automatically spilling into neighboring cells.
If you try these functions and get a #SPILL error, you’ve got something blocking the output range. Clear the cells below/beside your formula.
Dynamic arrays changed Excel fundamentally. Functions can now return tables, not just single values. Once you get comfortable with this, you’ll build reports completely differently.
Start Small
Don’t try to rebuild your entire financial model with these functions tomorrow. Pick one repetitive task:
- A monthly data extract you copy-paste
- A lookup formula you have to drag down 500 rows
- A calculation you repeat with slight variations across tabs
Solve that one problem with FILTER, XLOOKUP, or LET. See how it feels. Then find the next one.
Excel’s evolved. Your workflow should too.
Mark Hendy is a finance professional and interim CFO specializing in private equity-backed businesses. He writes about practical finance operations at tanous.co.uk.