Home › Blog › XLOOKUP & Dynamic Arrays: The Modern Formulas That Pay Like a Promotion
XLOOKUP & Dynamic Arrays: The Modern Excel Formulas That Pay Like a Promotion
VLOOKUP got you this far. The formulas that replaced it are what hiring managers now look for — and what quietly separates a spreadsheet user from an analyst.
Two analysts get the same ask: “Pull every product that’s below target this quarter, sorted worst-first, and put each rep’s commission next to it.” The first builds a helper column, nests an IF inside a VLOOKUP, sorts by hand, and prays the column count never changes. The second types three formulas, presses Enter, and the answer spills down the sheet — and updates itself the moment the data does.
Same Excel. Same data. One of them looks like they’re fighting the software; the other looks like they own it. That second look is what gets people promoted — and it comes down to a handful of modern formulas most office workers still haven’t learned.
What actually changed in the formula bar
For twenty years, VLOOKUP was the badge of an “Excel person.” It still works — but it’s brittle. It only looks right, breaks the moment someone inserts a column, and forces awkward workarounds to look left. Microsoft 365 replaced it with two genuinely new ideas:
- XLOOKUP — one function that looks in any direction, returns an exact match by default, and lets you set a “not found” message without wrapping it in
IFERROR. - Dynamic arrays — a single formula that returns many results at once and “spills” them across the cells below it.
FILTER,SORT,UNIQUE, andSEQUENCEturn what used to be a multi-step manual chore into one self-updating formula.
This isn’t cosmetic. XLOOKUP has rapidly become the default lookup for new workbooks, and dynamic arrays changed how the best analysts build everything from dashboards to staffing models. Knowing them signals you learned Excel recently — and that you keep up.
The tell: If your spreadsheets are full of helper columns, hidden tabs, and formulas you’re afraid to touch, you’re doing manually what one modern formula now does on its own — and refreshes automatically.
Three modern formula skills that mark you as a pro
1. Replace VLOOKUP with XLOOKUP
Instead of counting columns and hoping nobody reorders them, you name the lookup column and the return column directly: =XLOOKUP(A2, Names, Salaries). It matches exactly by default, looks left as easily as right, and takes a built-in fallback — =XLOOKUP(A2, Names, Salaries, "Not found"). Fewer moving parts means fewer 4:55-on-a-Friday errors.
2. Let one formula do the sorting and filtering
This is the leap. =SORT(FILTER(Data, Sales<Target), 2) returns every below-target row, already sorted — no manual filter, no copy-paste, no re-sorting next month. Pair UNIQUE with SORT to build a clean, self-updating list of customers or categories in one cell. When the source data changes, the result changes with it.
3. Write formulas a human can read with LET
LET lets you name a calculation once and reuse it, so a sprawling nested formula becomes something you — and the next person — can actually follow. Readable formulas get trusted, reused, and handed to you because people know yours won’t silently break.
The differentiator isn’t “Can you write a lookup?” It’s “Can you build one formula that does the whole job and maintains itself?”
Why employers pay a premium for it
Excel isn’t fading — it showed up in well over half a million job postings in a 2025 analysis, out-listing several popular programming languages. But the bar inside those postings has risen. Lower-paying listings say “proficient in Excel.” Higher-paying ones name the modern toolkit: dynamic arrays, advanced lookups, and clean, auditable formulas.
The numbers track that split. A data analyst with Excel skills averages around $65,800, but seasoned analysts clear $80,000+, and roles that demand genuinely advanced Excel proficiency can pay up to 20% more than ones that only expect general office software. The people at the top of that range aren’t writing more formulas — they’re writing better, modern ones.
There’s a reason for the premium: scarcity. Most spreadsheet users still reach for VLOOKUP and manual sorting because that’s what they were taught years ago. The supply of people fluent in dynamic arrays hasn’t caught up with the demand. Scarcity is leverage at review time.
How it translates to your career
- Raises: “I rebuilt our reporting so it updates itself instead of taking me a morning each week” is a concrete, defensible win — the kind that justifies a bump.
- Promotions: Modern formulas are the bridge from “the person who fills in the spreadsheet” to “the person who designs it.” That’s the analyst job, one rung up.
- Job security: Brittle, manual spreadsheets are exactly what gets automated away. The person who builds the self-maintaining version is the one who’s protected.
Learning these by trial and error is slow, because the old habits are hard to unlearn on your own. Structured training is the fastest path. Our Excel Formulas and Functions class takes you from VLOOKUP-and-helper-columns to confident XLOOKUP and dynamic arrays, and pairs perfectly with the MO-210 Excel Associate Certification Bootcamp — the credential hiring managers screen for.
Open a workbook with a real table. In an empty cell, type =FILTER( and select your data range, then add a simple condition — for example =FILTER(A2:D200, C2:C200>1000) to pull every row over 1,000. Press Enter and watch the results spill down on their own. Then wrap it in SORT — =SORT(FILTER(A2:D200, C2:C200>1000), 3, -1) — to rank them. You just did in one formula what used to take a filter, a sort, and a copy-paste. Change a number in the source and watch it update itself.
Trade brittle formulas for a raise-worthy skill
Every helper column and hand-sorted report is time you’re not spending on the analysis that gets you noticed — and a gap between you and the modern Excel skills employers pay extra for. See our upcoming schedule and grab a seat in the Formulas and Functions class.
See the class calendar → Explore the Formulas class →