Advanced Formulas
Array formulas, LAMBDA, dynamic arrays, and error handling — the formulas that separate competent Excel users from the ones everyone calls for help.
A data analyst at Netflix told an interviewer: "The person who knows FILTER, SORT, and UNIQUE gets hired. The person who only knows VLOOKUP gets screened out."
In 2018, Microsoft introduced dynamic arrays to Excel — a set of functions that fundamentally changed how formulas work. Before dynamic arrays, a single formula produced a single result in a single cell. After dynamic arrays, a single formula can return an entire table of results that spill across multiple cells.
It was the biggest change to Excel formulas in 30 years. And most people still do not know these functions exist.
This module covers the advanced formulas that separate the "I know Excel" crowd from the "People come to me with Excel problems" crowd. These are not academic exercises — they are the formulas used daily at companies like Netflix, Goldman Sachs, and McKinsey.
Dynamic arrays: FILTER, SORT, UNIQUE, SORTBY, SEQUENCE, RANDARRAY
Before dynamic arrays, extracting a filtered subset of data required helper columns, complex array formulas entered with Ctrl+Shift+Enter, or pivot tables. Now a single formula does it.
FILTER — extract rows that match criteria
=FILTER(array, include, [if_empty])
Example: Show all orders over $1,000:
=FILTER(A2:D100, D2:D100>1000, "No results")
This returns every matching row — automatically spilling into as many cells as needed. No helper columns. No pivot tables. One formula.
SORT — sort results dynamically
=SORT(array, [sort_index], [sort_order])
Example: Sort the customer list by revenue, descending:
=SORT(A2:C50, 3, -1)
The 3 means "sort by the third column" and -1 means descending. Combine with FILTER:
=SORT(FILTER(A2:D100, B2:B100="Enterprise"), 4, -1)
This filters to Enterprise customers AND sorts by amount — in one formula.
UNIQUE — remove duplicates dynamically
=UNIQUE(array)
Example: Get a list of all unique regions:
=UNIQUE(B2:B500)
Unlike Remove Duplicates (which is destructive), UNIQUE creates a live, dynamic list. Add a new row with a new region, and it appears in the UNIQUE output automatically.
✗ Without AI
- ✗VLOOKUP + helper columns to extract matching rows
- ✗Copy data, Sort, manual process
- ✗Remove Duplicates (destructive, one-time)
- ✗Ctrl+Shift+Enter array formulas
✓ With AI
- ✓=FILTER(data, criteria) — one formula
- ✓=SORT(data, column, order) — one formula
- ✓=UNIQUE(range) — live, non-destructive
- ✓Just press Enter — spills automatically
Dynamic Array Challenge
25 XPLAMBDA — create your own reusable functions (no VBA needed)
LAMBDA lets you define custom functions directly in the Name Manager — no VBA, no macros, no .xlsm file required.
=LAMBDA(parameter1, parameter2, calculation)
Example: Create a function called TAXAMOUNT that calculates tax:
- Go to Formulas → Name Manager → New
- Name:
TAXAMOUNT - Refers to:
=LAMBDA(amount, rate, amount * rate) - Click OK
Now you can use =TAXAMOUNT(B2, 0.08) anywhere in the workbook — just like a built-in function.
More practical LAMBDAs:
| Custom function | LAMBDA definition | Usage |
|---|---|---|
MARKUP | =LAMBDA(cost, pct, cost * (1 + pct)) | =MARKUP(A2, 0.3) → adds 30% markup |
BETWEEN | =LAMBDA(val, lo, hi, AND(val>=lo, val<=hi)) | =BETWEEN(B2, 10, 50) → TRUE/FALSE |
FULLNAME | =LAMBDA(first, last, first & " " & last) | =FULLNAME(A2, B2) |
Nested IFs vs IFS — cleaning up decision logic
Nested IF formulas are one of the most painful things in Excel:
=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
Count the parentheses. Now imagine adding another condition. IFS fixes this:
=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", A2>=60, "D", TRUE, "F")
| Feature | Nested IF | IFS |
|---|---|---|
| Readability | Gets worse with each condition | Stays flat and scannable |
| Max conditions | 64 nested (theoretical) | 127 condition pairs |
| Default value | Last FALSE argument | Use TRUE, "default" as last pair |
| Availability | All Excel versions | Excel 2019+, Microsoft 365 |
SWITCH is another alternative when you are matching exact values (not ranges):
=SWITCH(A2, "NY", "New York", "CA", "California", "TX", "Texas", "Unknown")
There Are No Dumb Questions
"Should I replace all my nested IFs with IFS?"
If your file needs to work in Excel 2016 or earlier, keep nested IFs. If everyone uses Excel 2019+ or Microsoft 365, switch to IFS for readability. For simple two-outcome decisions, regular IF is still the best choice — IFS is overkill for
=IF(A2>0, "Positive", "Not positive")."What does the TRUE at the end of IFS do?"
TRUEalways evaluates to TRUE, so it acts as a catch-all default — like the "else" in programming. If none of the preceding conditions match, theTRUEcondition triggers and returns the associated value. Without it, IFS returns a #N/A error if no condition matches.
Text functions — parsing and transforming strings
| Function | What it does | Example | Result |
|---|---|---|---|
=LEFT(A1, 3) | First N characters | =LEFT("Excel", 3) | "Exc" |
=RIGHT(A1, 4) | Last N characters | =RIGHT("Report2024", 4) | "2024" |
=MID(A1, 3, 5) | Characters from position | =MID("ABCDEFG", 3, 5) | "CDEFG" |
=LEN(A1) | Character count | =LEN("Hello") | 5 |
=TRIM(A1) | Removes extra spaces | =TRIM(" Hello World ") | "Hello World" |
=TEXTJOIN(",", TRUE, A1:A5) | Joins text with delimiter | =TEXTJOIN(", ", TRUE, A1:A3) | "Jan, Feb, Mar" |
=TEXTSPLIT(A1, ",") | Splits text by delimiter | =TEXTSPLIT("a,b,c", ",") | a | b | c (spills) |
=SUBSTITUTE(A1, "old", "new") | Replace specific text | =SUBSTITUTE(A1, " ", "-") | Replaces spaces with dashes |
Real-world combo: Extract the first name from "Smith, John":
=MID(A1, FIND(",", A1) + 2, LEN(A1))
This finds the comma position, moves 2 characters forward (past the comma and space), and extracts everything after.
Formula Power Challenge
50 XPDate functions — time-based calculations
| Function | What it does | Example |
|---|---|---|
=TODAY() | Current date | Returns today's date (updates daily) |
=NOW() | Current date and time | Returns current timestamp |
=YEAR(A1) | Extracts year | =YEAR("3/15/2024") → 2024 |
=MONTH(A1) | Extracts month | =MONTH("3/15/2024") → 3 |
=DATEDIF(A1, B1, "d") | Days between dates | =DATEDIF("1/1/2024","3/15/2024","d") → 74 |
=NETWORKDAYS(A1, B1) | Business days between dates | Excludes weekends |
=EDATE(A1, 3) | Date N months from date | 3 months after start date |
=EOMONTH(A1, 0) | Last day of the month | End of current month |
=WEEKDAY(A1) | Day of week (1-7) | 1 = Sunday through 7 = Saturday |
Error handling — making formulas bulletproof
Nothing looks more unprofessional than a spreadsheet full of #N/A, #DIV/0!, and #VALUE! errors. Error-handling formulas catch these before anyone sees them.
| Error | What causes it | Fix |
|---|---|---|
#N/A | VLOOKUP cannot find the value | Use XLOOKUP with if_not_found, or wrap in IFNA |
#DIV/0! | Dividing by zero | =IF(B2=0, 0, A2/B2) or =IFERROR(A2/B2, 0) |
#VALUE! | Wrong data type in formula | Check for text in number columns |
#REF! | Referenced cell was deleted | Fix the broken reference |
#NAME? | Typo in function name | Check spelling |
The two key error handlers:
=IFERROR(formula, value_if_error) — catches ANY error and returns an alternative value.
=IFNA(formula, value_if_na) — catches only #N/A errors (better for lookups — lets other errors surface so you can fix them).
Example: =IFERROR(VLOOKUP(A2, data, 3, FALSE), "Not found")
There Are No Dumb Questions
"Should I just wrap everything in IFERROR?"
No — and this is important. IFERROR hides ALL errors, including ones that indicate real problems in your data. If a formula returns #VALUE! because someone put text in a number column, you want to see that error so you can fix the data. Use IFNA for lookups (where #N/A is expected for missing values) and IFERROR sparingly and intentionally.
Key takeaways
- Dynamic arrays (FILTER, SORT, UNIQUE) replaced dozens of legacy techniques with single formulas that spill results automatically
- LAMBDA creates reusable custom functions without VBA — stored in Name Manager, works in regular .xlsx files
- IFS replaces messy nested IF formulas with flat, readable condition-value pairs — use
TRUEas the catch-all default - Text functions (LEFT, RIGHT, MID, FIND, SUBSTITUTE, TEXTJOIN, TEXTSPLIT) parse and transform string data for cleanup and extraction
- Date functions calculate business days, extract components, and perform time-based math — remember dates are stored as serial numbers
- Error handling with IFERROR and IFNA prevents ugly errors in reports — but use IFNA for lookups and avoid masking real errors with blanket IFERROR
Knowledge Check
1.You write =FILTER(A2:D500, B2:B500="West") and press Enter. What happens?
2.What is the primary advantage of LAMBDA over custom VBA functions?
3.What is the problem with wrapping every formula in IFERROR?
4.You need to convert scores to letter grades: 90+ = A, 80-89 = B, 70-79 = C, 60-69 = D, below 60 = F. Which formula is the cleanest approach?