Advanced Formulas

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.

LAMBDA — 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:

1. Go to Formulas → Name Manager → New
2. Name: TAXAMOUNT
3. Refers to: =LAMBDA(amount, rate, amount * rate)
4. Click OK

Now you can use =TAXAMOUNT(B2, 0.08) anywhere in the workbook — just like a built-in function.

More practical LAMBDAs:

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")

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?"

TRUE always evaluates to TRUE, so it acts as a catch-all default — like the "else" in programming. If none of the preceding conditions match, the TRUE condition triggers and returns the associated value. Without it, IFS returns a #N/A error if no condition matches.

Text functions — parsing and transforming strings

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.

Date functions — time-based calculations

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.

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 TRUE as 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