Advanced Excel Formulas: 25+ Formulas with Examples (Free PDF)

What makes an Excel formula advanced?

A formula is “advanced” when it does one of four things a basic SUM or AVERAGE can’t:

• It works with conditions: totals, counts or averages only for the rows that match one or more criteria (SUMIFS, COUNTIFS, MAXIFS).
• It finds data in another table: lookups by a key such as an invoice number or an employee ID (XLOOKUP, INDEX and MATCH).
• It returns a whole list, not one cell: the dynamic array functions FILTER, UNIQUE, SORT and SEQUENCE “spill” their results into the cells below.
• It combines steps: nesting functions, naming parts of a formula with LET, or turning a formula into your own function with LAMBDA.

If you are still getting comfortable with cell references, percentages and SUMIF, start with our guide to Excel formulas for accounting and come back to this one.

Which Excel version do you need for each formula?

This is the step most formula lists skip. The table below is based on the “Applies to” section and notes of each function’s page on Microsoft Support (checked 16 October 2026). Where the two disagree, we follow the note: Microsoft’s XLOOKUP page, for example, lists Excel 2016 and 2019 under “Applies to” but its note says XLOOKUP is not available in them.

To check your version, open File, then Account, then About Excel. Some offices still use Excel 2016 or 2019, so for a file you share, stick to the formulas in the first two rows unless you know everyone who opens it has a newer version.

The sample data used in every example

All the examples use one small sales register on a sheet with headers in row 1 and ten invoices in rows 2 to 11. The customers are invented; the numbers are chosen so you can check each result by hand.

Column H is =F2*G2 copied down, and the ten amounts add up to ₹4,93,500.

Lookup formulas: XLOOKUP, INDEX and MATCH, XMATCH

Lookups pull a value from a table using a key, such as fetching an invoice amount from its number. They are the formulas interviewers ask about most.

XLOOKUP

XLOOKUP is Excel’s newer lookup function: it searches one column for a value and returns the matching value from another. =XLOOKUP("INV-106",A2:A11,H2:H11,"Not found") returns 22,000, the amount of invoice INV-106. The fourth argument is the text shown when the invoice doesn’t exist, so you don’t need a separate IFERROR.

XLOOKUP can also return several columns at once: =XLOOKUP("INV-106",A2:A11,C2:E11) spills Gupta & Sons, East, Monitor into three cells side by side. Unlike VLOOKUP, the return column can be to the left of the lookup column, and inserting a column doesn’t break the formula. Syntax and options are on Microsoft’s XLOOKUP page.

INDEX and MATCH

For files that must open in Excel 2016 or 2019, INDEX with MATCH does the same job: =INDEX(H2:H11,MATCH("INV-106",A2:A11,0)) also returns 22,000. MATCH finds the row position (6), and INDEX returns the value in that position. The 0 asks for an exact match; leave it out and you can get wrong answers on unsorted data.

A two-way lookup uses one MATCH for the row and one for the column. =INDEX(A2:H11,MATCH("INV-103",A2:A11,0),MATCH("Qty",A1:H1,0)) returns 5, the quantity on invoice INV-103, and keeps working if someone reorders the columns.

XMATCH

XMATCH returns a position like MATCH but can search from the bottom. =XMATCH("Monitor",E2:E11) returns 3 (the first Monitor, INV-103), while =XMATCH("Monitor",E2:E11,0,-1) returns 10 (the last one, INV-110). Searching from the bottom is how you find the latest entry for an item in a register.

VLOOKUP vs XLOOKUP vs INDEX and MATCH

All three find a value by key. The difference is how safely they do it:

In practice: use XLOOKUP when everyone has Excel 2021 or later, INDEX and MATCH when the file must open in older versions, and read VLOOKUP when you inherit it.

Conditional totals: SUMIFS, COUNTIFS, MAXIFS, AVERAGEIFS, SUMPRODUCT

These answer questions like “how much did the North region sell in April?” without a filter or a pivot table.

SUMIFS with two conditions

=SUMIFS(H2:H11,D2:D11,"North",E2:E11,"Laptop") returns 2,60,000: the two North laptop invoices, INV-101 and INV-109. The first range is what you add up; after that come pairs of range and condition, and a row is counted only if it meets every condition.

SUMIFS between two dates

To total a period, use the date column twice: =SUMIFS(H2:H11,D2:D11,"North",B2:B11,">="&DATE(2026,4,1),B2:B11,"<="&DATE(2026,4,30)) returns 1,68,500, North’s April sales. Joining the operator to DATE() with & avoids the date-format mistakes that come from typing “01/04/2026” inside quotes.

COUNTIFS, MAXIFS and AVERAGEIFS

• =COUNTIFS(H2:H11,">=50000") returns 4: the invoices of ₹50,000 or more.
• =MAXIFS(H2:H11,D2:D11,"East") returns 28,500: the largest East invoice. MINIFS works the same way for the smallest.
• =AVERAGEIFS(H2:H11,E2:E11,"Monitor") returns 40,333.33: the average of the three Monitor invoices.

SUMPRODUCT

SUMPRODUCT multiplies ranges row by row and adds the results, so it can calculate and filter in one step. =SUMPRODUCT((E2:E11="Laptop")*F2:F11*G2:G11) returns 3,12,000: quantity times rate for laptop rows only, without needing the Amount column. The comparison in brackets returns 1 for matching rows and 0 for the rest, which is what does the filtering.

Logic formulas: IFS, SWITCH, IFERROR

IFS instead of nested IF

=IFS(H2>=100000,"Large",H2>=25000,"Medium",TRUE,"Small") labels each invoice by size. For row 2 (₹1,04,000) it returns Large; copied down, INV-102 (₹28,500) is Medium and INV-104 (₹9,000) is Small. Conditions are checked in order, so put the biggest threshold first, and end with TRUE as the catch-all.

SWITCH for exact matches

When you are matching one cell against a fixed list, SWITCH is shorter: =SWITCH(D2,"North","N-01","South","S-01","East","E-01","West","W-01","Unknown") turns region names into branch codes and returns N-01 for row 2. The last argument is the default when nothing matches.

IFERROR

IFERROR replaces an error with a value of your choice. =IFERROR(H2/F2,0) returns the rate per unit, or 0 if the quantity cell is empty. Use it carefully: it hides every error, including real mistakes, so in a lookup prefer XLOOKUP’s own “if not found” argument, which only catches missing keys.

Dynamic array formulas: FILTER, UNIQUE, SORT, SEQUENCE

Dynamic array formulas are formulas that return a list which “spills” into the cells below or beside the formula. You type the formula in one cell, and the result grows or shrinks when the data changes. If the cells in the way are not empty, Excel shows #SPILL! instead; clear them and the result appears. Microsoft’s guide to dynamic array formulas and spilled array behaviour explains the rules.

FILTER

=FILTER(A2:H11,C2:C11="Sharma Traders","No sales") returns the three Sharma Traders invoices, INV-101, INV-104 and INV-109, with all eight columns. For two conditions, multiply them: =FILTER(A2:H11,(D2:D11="North")*(H2:H11>=50000)) returns INV-101, INV-103 and INV-109. Always fill in the third argument: according to Microsoft’s FILTER page, an empty result without it gives a #CALC! error.

UNIQUE

=UNIQUE(C2:C11) returns the five customers, each once: Sharma Traders, Gupta & Sons, Mehta Stores, Verma Agencies, Kapoor & Co. Wrap it in COUNTA to count them: =COUNTA(UNIQUE(C2:C11)) returns 5. This replaces Remove Duplicates, which changes your data, with a list that updates itself.

SORT and SORTBY

=SORT(A2:H11,8,-1) returns the whole table sorted by column 8 (Amount) from largest to smallest, starting with INV-109 (₹1,56,000). SORTBY sorts one range by another: =SORTBY(C2:C11,H2:H11,-1) lists the customer of each invoice from the largest sale down, without showing the amounts.

SEQUENCE

SEQUENCE generates a list of numbers. =SEQUENCE(12) returns 1 to 12 down a column. Combined with DATE, it builds a month list for a financial year: =DATE(2026,3+SEQUENCE(12),1) returns the first day of each month from 1 April 2026 to 1 March 2027 (format the cells as dates). DATE rolls month 13 over into January of the next year, which is why this works.

Text formulas: TEXTJOIN, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, TRIM

Text formulas clean and combine the messy data you get from bank statements and software exports.

• TEXTJOIN: =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(C2:C11,D2:D11="North"))) returns Sharma Traders, Mehta Stores: every North customer, once, in one cell. TRUE skips empty cells.
• TEXTSPLIT: if J2 holds a bank narration such as NEFT/UTR12345/SHARMA TRADERS, =TEXTSPLIT(J2,"/") spills NEFT, UTR12345, SHARMA TRADERS into three cells.
• TEXTBEFORE and TEXTAFTER: =TEXTBEFORE(J2,"/") returns NEFT, and =TEXTAFTER(J2,"/",-1) returns SHARMA TRADERS; the -1 means “after the last slash”.
• TRIM and CLEAN: =TRIM(CLEAN(A2)) removes extra spaces and non-printing characters, a common reason two values that look the same don’t match in a lookup.

Date formulas: EOMONTH, EDATE, NETWORKDAYS

• EOMONTH: =EOMONTH(B5,0) returns 30-Apr-2026, the last day of the month of INV-104 (12 April). Use 1 for the next month-end, -1 for the previous one. Month-end dates are what you group sales and dues by.
• EDATE: =EDATE(B5,1) returns 12-May-2026, the same day one month later, which suits monthly due dates. For a due date in days, simply add: =B5+30 also gives 12-May-2026 here, because April has 30 days.
• NETWORKDAYS: =NETWORKDAYS(DATE(2026,4,1),DATE(2026,4,30)) returns 22, the Monday-to-Friday days in April 2026. Add a range of holiday dates as the third argument to exclude them too.

Reshaping data: VSTACK, CHOOSECOLS, TAKE and DROP

These newer functions (Excel 2024 and Microsoft 365) rearrange whole tables with a formula, which used to need copy and paste or Power Query.

• VSTACK: =VSTACK(April!A2:H8,May!A2:H4) stacks the April and May sheets into one list. Change either sheet and the combined list updates.
• CHOOSECOLS: =CHOOSECOLS(A2:H11,1,3,8) returns only Invoice, Customer and Amount, in that order.
• TAKE: =TAKE(SORT(A2:H11,8,-1),3) returns the top three invoices by amount: INV-109, INV-101 and INV-103. A negative number takes from the bottom.
• DROP: =DROP(A1:H11,1) removes the header row, handy before stacking several tables.

LET and LAMBDA: name parts of a formula and build your own

LET

LET is a function that gives names to parts of a formula, so a long formula reads like a sentence and repeated parts are calculated once. =LET(amt,H2:H11,north,D2:D11="North",SUM(FILTER(amt,north))) returns 3,24,500, total North sales. Each name is followed by its value, and the last argument is the calculation that uses them.

LAMBDA

LAMBDA is a function that turns a formula into a function you can reuse by name. In Formulas, then Name Manager, create a name such as NETPRICE that refers to =LAMBDA(price,disc,price*(1-disc)). After that, =NETPRICE(G2,10%) returns 46,800, the laptop rate after a 10% discount. The calculation lives in one place, so changing it updates every cell that uses it. The rules are on Microsoft’s LAMBDA page.

GROUPBY: a summary table without a pivot table

GROUPBY (Microsoft 365) summarises data with a single formula. =GROUPBY(D2:D11,H2:H11,SUM) returns sales by region with a grand total, which is the default according to Microsoft’s GROUPBY page:

Unlike a pivot table, it recalculates the moment the data changes, with no Refresh. For drag-and-drop summaries with filters and slicers, a pivot table is still the better tool; our guide to pivot tables in Excel covers it.

Advanced Excel formulas list: one-page summary

Common formula errors and how to fix them

Practice exercises (with answers)

Try these on the practice workbook before looking at the answers. Each one is answered by a formula from this page.

1. Total sales to Gupta & Sons. Answer: =SUMIFS(H2:H11,C2:C11,"Gupta & Sons") returns 50,500.
2. How many Monitor invoices are there? Answer: =COUNTIFS(E2:E11,"Monitor") returns 3.
3. The customer on the largest invoice. Answer: =XLOOKUP(MAX(H2:H11),H2:H11,C2:C11) returns Sharma Traders.
4. All invoices from May. Answer: =FILTER(A2:H11,MONTH(B2:B11)=5,"None") returns INV-108, INV-109 and INV-110.
5. Total quantity of keyboards sold. Answer: =SUMIFS(F2:F11,E2:E11,"Keyboard") returns 25.

Download the PDF and the practice workbook

Two free files go with this guide:

• Advanced Excel formulas cheat sheet (PDF): the one-page list above with syntax and the version table, to print or keep on your phone.
• Practice workbook (Excel file): the sample sales sheet, every example formula from this page with the result it should return, and the five exercises. Type each formula yourself and check your answer against the expected result.

The newer formulas need Excel 2021 or later (some need Excel 2024 or Microsoft 365); in an older version they show #NAME?, which is itself a quick way to check what you have.

Where these formulas are used at work

In accounts and MIS jobs, a handful of these do most of the work:

• Accounts: XLOOKUP or INDEX and MATCH to match a purchase register with supplier statements, SUMIFS for party-wise and month-wise totals, and EOMONTH for ageing.
• MIS reporting: FILTER, UNIQUE and SORT for self-updating lists, and SUMIFS or GROUPBY for summary sheets. Our guide to the MIS course and MIS executive jobs explains the role.
• Interviews: employers often give a short Excel test with a lookup, a conditional total and a pivot table. See our accounting interview questions for the rest of the round.

If you want to learn with a trainer, IPA’s Advanced Excel course in Laxmi Nagar, Delhi covers lookups (VLOOKUP and INDEX-MATCH), IF logic, COUNTIF and SUMIF, pivot tables, Power Query, dashboards, and macros with VBA.

Frequently asked questions