Advanced Excel Formulas With Examples

The formulas that actually matter

Most people stop at SUM and VLOOKUP and call it a day. That works fine until your dataset grows past a few thousand rows and everything slows to a crawl. I learned this the hard way back in 2019 when I inherited a financial model with over 40,000 rows of transaction data. The analyst before me had chained together fifteen nested VLOOKUPs across four separate sheets. Every time someone opened the file, it took about forty seconds to load. Not great for a quarterly close.

The fix wasn't to learn fancier functions. It was to replace those VLOOKUPs with INDEX and MATCH, then wrap them in XLOOKUP where possible. Load time dropped to six seconds. That single change alone saved me roughly three hours per month on model refreshes. I still see people manually matching two columns of data like it's 2012. It's not. INDEX and MATCH replaced VLOOKUP for most of us years ago. The difference is subtle on the surface but massive in practice. VLOOKUP can only search left-to-right. It requires your lookup value to be in the first column of your range. That constraint forces bad data layout decisions. INDEX MATCH removes that constraint entirely. You can look up values in any column and return results from any other column. Here is what that looks like in practice: =INDEX(B2:B1000,MATCH(E2,A2:A1000,0))

This finds the value in E2 within column A, then returns the corresponding value from column B. The third argument set to zero means exact match. If you leave it out, Excel defaults to approximate match, which will silently return wrong values if your data is not sorted. That silent failure is what gets people in trouble. Dynamic arrays changed how I approach almost everything in Excel. When you enter a formula like =UNIQUE(A2:A500) into a single cell, Excel spills the results into as many cells as needed below it. No more copy-paste-unique tricks. No more helper columns. The same applies to FILTER, SORT, and SEQUENCE. These functions are part of what people mean when they talk about Advanced Excel Formulas With Examples now. They have fundamentally reduced the need for array-entering formulas with Ctrl+Shift+Enter, which was always a source of errors for anyone not paying close attention. I ran into a real problem last year with a forecasting model that used FILTER to extract records matching multiple conditions. The formula looked like this:

=FILTER(Data!A2:Z1000,(Data!B2:B1000="Q3")*(Data!C2:C1000>5000)) The multiplication operator works as a logical AND here. That part is standard. But the issue came when one of those filter criteria returned zero matching rows. Instead of returning an empty array, Excel threw a #CALC! error that broke every downstream calculation. The workaround was wrapping the whole thing in IFERROR, which gave me a clean fallback: =IFERROR(FILTER(Data!A2:Z1000,(Data!B2:B1000="Q3")*(Data!C2:C1000>5000)),"No matches found")

This is the kind of edge case nobody warns you about until it costs you an evening of debugging. Sumproduct with conditional logic deserves more attention than it gets. It handles weighted averages, conditional sums, and cross-sheet lookups that even XLOOKUP struggles with. I use it constantly for budget reconciliation work where I need to sum values across multiple sheets based on product category, region, and date range simultaneously. =SUMPRODUCT((Region="West")*(Category="Software")*(Amount))

This sums all Amount values where Region equals West and Category equals Software. No helper columns. No pivots. Just one formula that does the filtering and the summing at the same time. The tradeoff is performance. Sumproduct on ranges larger than 50,000 rows will noticeably slow down your file, especially if you have several instances of it in the same worksheet. I switch to a Power Query solution for anything that big. Text functions combined with LET is where things get interesting. The LET function lets you assign names to calculation results within a single formula. This makes complex formulas readable and faster because Excel evaluates each named part only once. Before LET existed, I would write the same sub-calculation three or four times in a single formula. Now I name it once and reference it repeatedly. The performance gain is real but small. The readability gain is enormous. =LET(fulltext,A2,words,TEXTSPLIT(fulltext," "),count,COUNTA(words),first,INDEX(words,1),first&" has "&count&" words")

This takes a sentence in A2, splits it into individual words, counts them, grabs the first word, and returns a formatted string. One formula does what previously required four or five helper cells. I use this pattern constantly for data cleanup tasks. The output is exactly what I described, nothing more and nothing less. XLOOKUP replaced VLOOKUP for most workflows after Excel 2021. It handles exact matches by default. It searches in any direction. It supports error handling built in as a fourth argument. And it works with arrays natively. If you are still writing VLOOKUP formulas in a modern version of Excel, you are making life harder than it needs to be. The main limitation of XLOOKUP is backward compatibility. Files saved in older Excel formats break XLOOKUP functions. If you share models with people using Excel 2019 or earlier, stick to INDEX MATCH or keep a VLOOKUP fallback in a hidden sheet. There is a common misconception that nested IF statements are obsolete. They are not. For simple branching logic, IF is fine. But once you cross five levels of nesting, the formula becomes unmaintainable. I replace deep IF chains with SWITCH or XLOOKUP-based lookups instead. A SWITCH formula reads like a decision tree without the parentheses soup.

=SWITCH(A2,1,"Low",2,"Medium",3,"High","Unknown") This is cleaner and easier to audit than an equivalent nested IF structure. People often miss that SWITCH returns the default value when no match is found. That default argument eliminates the need for a catch-all final condition. One thing worth noting upfront is that none of these formulas solve the problem of poorly structured source data. If your raw transaction rows have inconsistent formatting, mixed data types, or missing values scattered throughout, a perfect XLOOKUP formula will still return errors or wrong results. I spend more time cleaning data than writing formulas now. Power Query handles the cleaning. The formulas handle the analysis. Keeping those two jobs separate in your workflow makes both parts faster and easier to debug.

There is also a limit to how much these functions will help if your underlying data model is flawed. A well-built INDEX MATCH or dynamic array formula cannot fix a dataset where the same product appears under three different names across different sheets. Data governance matters more than formula complexity. I have seen teams invest weeks optimizing spreadsheet logic only to realize the bottleneck was duplicated records, not slow calculations. If you want practical examples to work through, I recommend building a small test workbook with synthetic data rather than applying these formulas directly to live production files. A dataset of five hundred to a thousand rows is enough to stress-test any formula without risking corruption of real work. Write the formula, verify the output against manual spot checks, then scale up. That process takes me about ten minutes per new formula type and has prevented more errors than I care to admit.