30 Advanced Excel Formulas Analysts Use: Syntax and a Worked Example for Each

What sample data do the examples use?

Type this into a blank sheet with headers in row 1, so the data sits in A1:G11. Dates are real Excel dates, and Revenue = Units × Price.

A: OrderID	B: Date	C: Region	D: Rep	E: Product	F: Units	G: Revenue (₹)
1001	02-Apr-2026	North	Priya	Laptop	2	90,000
1002	05-Apr-2026	South	Rahul	Mouse	10	5,000
1003	09-Apr-2026	West	Priya	Monitor	3	36,000
1004	15-Apr-2026	North	Aman	Laptop	1	45,000
1005	20-Apr-2026	East	Rahul	Keyboard	5	7,500
1006	03-May-2026	South	Aman	Monitor	2	24,000
1007	08-May-2026	West	Priya	Mouse	8	4,000
1008	14-May-2026	North	Rahul	Laptop	3	1,35,000
1009	21-May-2026	East	Aman	Keyboard	4	6,000
1010	28-May-2026	South	Priya	Laptop	1	45,000

Add three small helper tables:

• Price list in J1:K5: Product / Price, then Laptop 45000, Monitor 12000, Keyboard 1500, Mouse 500.
• Commission slabs in M1:N4: Threshold / Rate, then 0 → 2%, 50000 → 3%, 100000 → 5%.
• Holidays in P1:P3: a header, then 03-Apr-2026 and 14-Apr-2026 (example company holidays).

Total revenue is ₹3,97,500. We checked every result below by recomputing it from this data in a script.

Which Excel version do you need for these formulas?

Formulas	Excel 2019	Excel 2021	Excel 2024	Microsoft 365
INDEX/MATCH, VLOOKUP, SUMIFS, COUNTIFS, AVERAGEIFS, SUMPRODUCT, TEXT, TRIM, EOMONTH, NETWORKDAYS.INTL	Yes	Yes	Yes	Yes
MAXIFS, MINIFS, IFS, SWITCH, TEXTJOIN	Yes	Yes	Yes	Yes
XLOOKUP, XMATCH, FILTER, UNIQUE, SORT, SORTBY, LET	No	Yes	Yes	Yes
TEXTBEFORE, TEXTAFTER, TEXTSPLIT	No	No	Yes	Yes
GROUPBY	No	No	No	Yes

Based on the "applies to" lists on Microsoft's support pages, checked September 2026. Excel for the web is free with a Microsoft account and is a practical way to try the newer functions if your installed version is older. If a formula shows #NAME?, your version probably does not have it.

Lookup formulas (1–8)

1. XLOOKUP (basic)

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

=XLOOKUP(1008, A2:A11, G2:G11)

Result: ₹1,35,000, the revenue of order 1008.

XLOOKUP replaces most VLOOKUP and HLOOKUP work. It looks left or right, defaults to an exact match, and does not break when someone inserts a column.

2. XLOOKUP with a "not found" message

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found)

=XLOOKUP("Tablet", J2:J5, K2:K5, "Not listed")

Result: "Not listed", because Tablet is not in the price list.

The fourth argument replaces the old IFERROR(VLOOKUP(...)) wrapper and makes missing values obvious instead of showing #N/A.

3. XLOOKUP approximate match for slabs

Syntax: match_mode -1 = exact match, or the next smaller item

=XLOOKUP(G9, M2:M4, N2:N4, , -1)

Result: 5%. Order 1008 is ₹1,35,000, and the largest threshold not above it is ₹1,00,000.

Use this for commission slabs, tax brackets or grade bands. The slab table does not need to be sorted when match_mode is -1 (sorting is only needed for binary search_mode 2 or -2).

4. XLOOKUP last match

Syntax: search_mode -1 = search from last to first

=XLOOKUP("Priya", D2:D11, B2:B11, , 0, -1)

Result: 28-May-2026, Priya's most recent order date (format the cell as a date).

Searching from the bottom returns the latest entry in a date-sorted log without any sorting or helper columns.

5. INDEX + MATCH

Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

=INDEX(K2:K5, MATCH("Monitor", J2:J5, 0))

Result: ₹12,000.

This works in every Excel version, which is why interviewers still ask for it. The 0 in MATCH means exact match; leaving it out defaults to an approximate match and is a common source of wrong answers.

6. INDEX + MATCH with two criteria

Syntax: =INDEX(return_range, MATCH(1, (range1=value1)*(range2=value2), 0))

=INDEX(G2:G11, MATCH(1, (C2:C11="South")*(E2:E11="Laptop"), 0))

Result: ₹45,000 (order 1010, the only South laptop order).

Multiplying the two TRUE/FALSE arrays gives 1 only where both conditions are true. In Excel 2019 and older, confirm with Ctrl+Shift+Enter. In Microsoft 365 it works as a normal formula.

7. XMATCH

Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

=XMATCH("Monitor", J2:J5)

Result: 2, because Monitor is the second item in the list.

XMATCH is the modern MATCH: it defaults to an exact match and can search from the end.

8. VLOOKUP with IFERROR (for older files)

Syntax: =IFERROR(VLOOKUP(lookup_value, table, col_index, FALSE), value_if_error)

=IFERROR(VLOOKUP("Tablet", J2:K5, 2, FALSE), "Not listed")

Result: "Not listed".

You will still meet VLOOKUP in company files. Always pass FALSE for an exact match, and remember that it only looks to the right of the first column.

Conditional sum, count and average formulas (9–14)

9. SUMIFS

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=SUMIFS(G2:G11, C2:C11, "North", E2:E11, "Laptop")

Result: ₹2,70,000 (orders 1001, 1004 and 1008).

10. SUMIFS between two dates

Syntax: Criteria built with ">="&date

=SUMIFS(G2:G11, B2:B11, ">="&DATE(2026,5,1), B2:B11, "<="&EOMONTH(DATE(2026,5,1),0))

Result: ₹2,14,000, total May 2026 revenue.

Join the operator and the date with &. Writing ">=01-05-2026" as text can be misread depending on the regional date format.

11. COUNTIFS

Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=COUNTIFS(D2:D11, "Priya", G2:G11, ">=40000")

Result: 2 (orders 1001 and 1010).

12. AVERAGEIFS

Syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

=AVERAGEIFS(G2:G11, E2:E11, "Laptop")

Result: ₹78,750, the average laptop order.

13. MAXIFS / MINIFS

Syntax: =MAXIFS(max_range, criteria_range1, criteria1, ...)

=MAXIFS(G2:G11, D2:D11, "Rahul")

Result: ₹1,35,000, Rahul's largest order. MINIFS works the same way.

Available from Excel 2019 onward. Before that, people used array formulas with MAX(IF(...)).

14. SUMPRODUCT for conditions SUMIFS cannot handle

Syntax: =SUMPRODUCT(array1, [array2], ...)

=SUMPRODUCT((MONTH(B2:B11)=4)*G2:G11)

Result: ₹1,83,500, total April revenue.

SUMIFS cannot apply a function such as MONTH to its criteria range. SUMPRODUCT can, because it works on arrays directly.

Dynamic array formulas (15–20)

Dynamic array formulas return several values that "spill" into the cells below or to the right. Leave those cells empty, or you will get a #SPILL! error.

15. FILTER

Syntax: =FILTER(array, include, [if_empty])

=FILTER(A2:G11, (C2:C11="North")*(G2:G11>50000), "None")

Result: Spills two full rows: orders 1001 and 1008.

Use * between conditions for AND and + for OR. Without the third argument, an empty result shows #CALC!.

16. UNIQUE

Syntax: =UNIQUE(array, [by_col], [exactly_once])

=UNIQUE(D2:D11)

Result: Spills Priya, Rahul, Aman, in order of first appearance.

17. SORT

Syntax: =SORT(array, [sort_index], [sort_order], [by_col])

=SORT(UNIQUE(C2:C11))

Result: East, North, South, West.

Wrapping one dynamic array function inside another is the everyday pattern, for example to build a clean dropdown source list.

18. SORTBY

Syntax: =SORTBY(array, by_array1, [sort_order1], ...)

=SORTBY(A2:A11, G2:G11, -1)

Result: Order IDs from highest to lowest revenue: 1008, 1001, then 1004 and 1010 (tied at ₹45,000), and so on.

Unlike SORT, the column you sort by does not have to be part of the output.

19. COUNTA + UNIQUE for a distinct count

Syntax: =COUNTA(UNIQUE(range))

=COUNTA(UNIQUE(E2:E11))

Result: 4 distinct products.

Before dynamic arrays this needed SUMPRODUCT(1/COUNTIF(...)) or a pivot table with the data model.

20. GROUPBY (Microsoft 365 only)

Syntax: =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

=GROUPBY(C2:C11, G2:G11, SUM)

Result: A spilled summary: East 13,500; North 2,70,000; South 74,000; West 40,000; and a Total row of 3,97,500.

GROUPBY is a formula-based pivot table that updates itself. Microsoft lists it for Excel for Microsoft 365 only, so avoid it in files shared with Excel 2021 or 2024 users.

Logic formulas (21–23)

21. IFS

Syntax: =IFS(test1, value1, [test2, value2], ...)

=IFS(G2>=100000, "Large", G2>=25000, "Medium", TRUE, "Small")

Result: "Medium" for order 1001 (₹90,000).

The final TRUE acts as "everything else". Without it, a value that matches no test returns #N/A.

22. SWITCH

Syntax: =SWITCH(expression, value1, result1, [value2, result2], ..., [default])

=SWITCH(C2, "North", "N-Zone", "South", "S-Zone", "Other")

Result: "N-Zone".

Use SWITCH when you compare one cell to a list of exact values, and IFS when each test is a different condition.

23. LET

Syntax: =LET(name1, value1, [name2, value2, ...], calculation)

=LET(north, SUMIFS(G2:G11, C2:C11, "North"),
     total, SUM(G2:G11),
     north/total)

Result: 0.679, so the North region is 67.9% of revenue.

LET names intermediate results. Long formulas become readable, and a value used twice is calculated once.

Text formulas (24–28)

24. TEXTJOIN

Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(E2:E11, D2:D11="Priya")))

Result: "Laptop, Monitor, Mouse", every product Priya sold, listed once each.

25. TEXT

Syntax: =TEXT(value, format_text)

=TEXT(B2, "mmm-yyyy")
="Revenue: "&TEXT(G9, "#,##0")

Result: "Apr-2026" and "Revenue: 135,000".

TEXT turns numbers and dates into labels for titles and dashboards. The result is text, so do not use it in further maths. Whether commas show in the lakh style depends on your format code and regional settings.

26. TEXTBEFORE and TEXTAFTER

Syntax: =TEXTBEFORE(text, delimiter, [instance_num], ...)

=TEXTBEFORE("LAP-2026-N", "-")
=TEXTAFTER("LAP-2026-N", "-", -1)

Result: "LAP" and "N". A negative instance_num counts from the end.

These replace fiddly LEFT/MID/FIND combinations for splitting codes, emails and file names. In older versions, =LEFT(A2, FIND("-", A2)-1) does the TEXTBEFORE job.

27. TEXTSPLIT

Syntax: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

=TEXTSPLIT("Mumbai|Delhi|Pune", "|")

Result: Spills Mumbai, Delhi and Pune into three cells side by side.

28. TRIM, CLEAN and PROPER for data cleaning

Syntax: =PROPER(TRIM(CLEAN(text)))

=PROPER(TRIM("  priya   sharma "))

Result: "Priya Sharma".

TRIM removes leading and trailing spaces and reduces repeated inner spaces to one. CLEAN removes non-printing characters that often come from system exports. Stray spaces are a common reason a lookup fails on values that look identical.

Date formulas (29–30)

29. EOMONTH

Syntax: =EOMONTH(start_date, months)

=EOMONTH(B2, 0)
=EOMONTH(B2, -1)+1

Result: 30-Apr-2026 (month end) and 01-Apr-2026 (month start).

EOMONTH is the cleanest way to build month buckets and date-range criteria, as in formula 10.

30. NETWORKDAYS.INTL

Syntax: =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

=NETWORKDAYS.INTL(DATE(2026,4,1), DATE(2026,4,30), 11, P2:P3)

Result: 24 working days. Weekend code 11 means Sunday only, which suits six-day work weeks. P2:P3 holds two company holidays, 3 and 14 April 2026, both weekdays, so 26 − 2 = 24.

The weekend argument can also be a 7-character string starting Monday, where 1 marks a day off: "0000011" means Saturday and Sunday off.

How should you practise these Excel formulas?

• Rebuild the sample sheet and type each formula yourself instead of copying it. Then change a value and predict the new result.
• Convert the data to a Table (Ctrl+T). Formulas such as =SUMIFS(Sales[Revenue], Sales[Region], "North") are easier to read and grow with the data.
• Solve one question three ways, for example North revenue with SUMIFS, SUMPRODUCT and a pivot table. Interviewers like candidates who know more than one route.
• Know when to stop using formulas. For repeated cleaning of large exports, Power Query or SQL is usually a better tool than ever-longer formulas.

If Excel is part of your interview preparation, go through our Excel interview questions with a practice test next. To choose structured training, see how to choose an advanced Excel course. Most analyst roles pair Excel with SQL, so our SQL interview questions guide is a natural follow-on.

Frequently Asked Questions

What are the most important advanced Excel formulas for data analysts?

XLOOKUP or INDEX/MATCH for lookups, SUMIFS and COUNTIFS for conditional totals, FILTER, UNIQUE and SORT for dynamic lists, IF or IFS for logic, TEXT and TRIM for cleaning, and EOMONTH for date ranges. Pivot tables sit alongside these as a core skill.

Is XLOOKUP better than VLOOKUP?

For most tasks, yes. XLOOKUP defaults to an exact match, can look left, has a built-in not-found argument and does not break when columns are inserted. VLOOKUP is still worth knowing because older files and Excel 2019 users rely on it.

Why does my Excel formula show #NAME?

Usually either the function name is misspelled or your Excel version does not include that function. For example, XLOOKUP, FILTER and LET are not available in Excel 2019, and GROUPBY is only in Microsoft 365.

What is a #SPILL! error in Excel?

A dynamic array formula such as FILTER or UNIQUE needs empty cells to spill its results into. If any of those cells contain data, Excel shows #SPILL!. Clear the blocking cells or move the formula.

Can I use these formulas for free?

Excel for the web is free with a Microsoft account, according to Microsoft, and includes many modern functions. The desktop app needs a Microsoft 365 subscription or a one-time Office licence; see Microsoft's pricing page for current plans.

Sources and methodology

Syntax and version availability come from Microsoft Support function pages, checked September 2026. Results were checked by recomputing each example from the sample data in a Python script; we did not have a licensed Excel install for this article, so run the sheet yourself if you spot a difference.

• Microsoft Support: XLOOKUP, XMATCH, FILTER, UNIQUE, SORTBY, LET, GROUPBY, checked September 2026.
• Microsoft Support: MAXIFS, IFS, SWITCH, TEXTJOIN, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, NETWORKDAYS.INTL, checked September 2026.
• Microsoft, Free Microsoft 365 for the web (free Excel in the browser with a Microsoft account), checked September 2026.

The sample data, holidays and slab rates are made up for illustration. The practice tips are ISS editorial guidance.

Next steps

Excel is Week 2 of the Data & Business Intelligence program (PivotTables, lookup formulas and a business revenue report), followed by SQL, Python and dashboards. The free Data Analyst Starter Kit on this page includes a skills checklist that covers Excel.

Applying is free, and you pay only after accepting an offer. Apply here.