What You Will Learn

By the end of this course, you will handle complex data tasks that most Excel users struggle with.

Master VLOOKUP, HLOOKUP, and the modern XLOOKUP

Use INDEX-MATCH for flexible two-way lookups

Apply SUMIFS, COUNTIFS, AVERAGEIFS with multiple conditions

Write IF, IFS, NESTED IF and logical combinations

Use text functions — LEFT, RIGHT, MID, FIND, SUBSTITUTE

Work with date and time formulas like DATEDIF, EOMONTH, WORKDAY

Understand and write array formulas (Ctrl+Shift+Enter)

Use dynamic array functions — FILTER, SORT, UNIQUE, SEQUENCE

Handle errors with IFERROR, IFNA, ISERROR

Apply conditional formatting with formulas

Build a complete automated report using advanced formulas

Understand named ranges and structured table references

Course Modules

Click any module to expand. Click any lesson for full details.

Your Progress 0 / 0 lessons

0% Complete

Lookup Functions — VLOOKUP, HLOOKUP & XLOOKUP

4 Lessons · Most Used Formulas

▼

▶

VLOOKUP — Deep Dive

Exact vs approximate match, common errors (#N/A, #REF!), and how to fix them.

▶

HLOOKUP — Horizontal Lookup

When your data is in rows, not columns — and when to use HLOOKUP.

▶

XLOOKUP — The Modern Replacement

Search any direction, return multiple columns, built-in error handling.

▶

Nested Lookups — Lookup within a Lookup

Use VLOOKUP or XLOOKUP result as input to another function.

INDEX & MATCH — The Power Combination

4 Lessons · Professional Level

▼

▶

INDEX Function — Return Any Value by Position

How INDEX works alone — returning values from a row, column, or intersection.

▶

MATCH Function — Find a Position

Exact match, approximate match, and match types explained with examples.

▶

INDEX + MATCH Together — Better than VLOOKUP

Left lookups, two-way lookups, and why this combo beats VLOOKUP.

▶

Two-Way Lookup with INDEX + MATCH

Find the intersection of a row and column dynamically.

Advanced Conditional Formulas

4 Lessons · Data Analysis

▼

▶

SUMIFS, COUNTIFS, AVERAGEIFS

Multiple condition aggregation — the backbone of any data report.

▶

Nested IF and IFS Function

Multiple conditions, cleaner syntax with IFS, and avoiding deep nesting.

▶

AND, OR, NOT with IF

Combine logical functions to create complex multi-condition rules.

▶

SWITCH Function — Clean Alternative to Nested IF

Match a value against a list and return corresponding results.

Text Functions

4 Lessons · Data Cleaning

▼

▶

LEFT, RIGHT, MID — Extract Text

Pull first name, last name, codes, or any part of a string.

▶

FIND, SEARCH, LEN — Locate & Measure Text

Find position of a character, count characters, case-sensitive vs insensitive.

▶

SUBSTITUTE, REPLACE — Modify Text

Replace specific text, remove unwanted characters, clean imported data.

▶

TEXTJOIN, CONCAT, TEXT — Combine & Format

Join cells with separators, format numbers/dates as readable text strings.

Date & Time Formulas

4 Lessons · Real-World Reporting

▼

▶

TODAY, NOW, DATE, YEAR, MONTH, DAY

Work with current date, extract parts of a date, build dates from components.

▶

DATEDIF — Calculate Age and Duration

Years, months, days between two dates — for age, tenure, deadlines.

▶

EOMONTH, EDATE — Shift Dates

Jump to end of month, add months to a date — for billing and scheduling.

▶

WORKDAY, NETWORKDAYS — Working Days

Exclude weekends and holidays from date calculations.

Array Formulas & Dynamic Arrays

4 Lessons · Advanced Level

▼

▶

Traditional Array Formulas (Ctrl+Shift+Enter)

What arrays are, how CSE formulas work, and when to use them.

▶

FILTER — Extract Rows Based on Conditions

Dynamically filter a table to show only matching rows — no helper columns.

▶

SORT, SORTBY, UNIQUE — Dynamic Sorting & De-duplication

Sort a list automatically, sort by multiple columns, extract unique values.

▶

SEQUENCE & RANDARRAY — Generate Number Arrays

Create numbered lists, random samples, and dynamic ranges automatically.

Error Handling, Named Ranges & Formula Auditing

4 Lessons · Professional Skills

▼

▶

IFERROR, IFNA, ISERROR — Handle Formula Errors

Catch #N/A, #DIV/0!, #VALUE! and show clean results instead.

▶

Named Ranges — Make Formulas Readable

Name a cell or range and use the name in formulas instead of addresses.

▶

Structured Table References

Reference Table columns by name — =SUM(SalesData[Amount]) instead of =SUM(B2:B500).

▶

Formula Auditing — Trace, Evaluate & Debug

Trace precedents/dependents, use Evaluate Formula step-by-step to debug complex formulas.

🏆 Bonus: Advanced Formula Cheat Sheet

The most important formulas at a glance.

Modern lookup any direction=XLOOKUP()

Flexible lookup — beats VLOOKUP=INDEX(MATCH())

Sum with multiple conditions=SUMIFS()

Count with multiple conditions=COUNTIFS()

Average with conditions=AVERAGEIFS()

Multiple IF without nesting=IFS()

Match value to a list=SWITCH()

Filter rows dynamically=FILTER()

Sort a list automatically=SORT()

Remove duplicates dynamically=UNIQUE()

Generate number sequence=SEQUENCE()

Extract text from left=LEFT(text, n)

Extract text from right=RIGHT(text, n)

Extract text from middle=MID(text, start, n)

Replace specific text=SUBSTITUTE()

Days between two dates=DATEDIF()

End of month date=EOMONTH(date, n)

Working days between dates=NETWORKDAYS()

Catch any formula error=IFERROR(formula, "")

Catch only #N/A error=IFNA(formula, "")

⚠️ Bonus: Common Advanced Formula Mistakes

These mistakes trip up even experienced Excel users.

Using VLOOKUP when column order changes

VLOOKUP breaks when you insert or delete columns in your lookup table. Use INDEX+MATCH or XLOOKUP instead — they reference column names, not positions.

Forgetting to lock ranges in SUMIFS/COUNTIFS

When copying SUMIFS formulas down a column, always use $A$2:$A$100 for the range arguments. Without $, the range shifts and gives wrong results.

Mixing text and numbers in VLOOKUP criteria

If your ID is stored as text in one place and as a number in another, VLOOKUP returns #N/A. Use TEXT() or VALUE() to convert before looking up.

Using DATEDIF incorrectly

DATEDIF(start, end, unit) — the start date must always come first. If end is earlier than start, it returns an error. Always check date order.

Array formulas without Ctrl+Shift+Enter (in older Excel)

Traditional array formulas need Ctrl+Shift+Enter to work in Excel 2016 and earlier. Missing this gives wrong results with no error warning.

FILTER and dynamic arrays spilling into filled cells

Dynamic array functions like FILTER and SORT need empty space below/right to spill results. If blocked by existing data, you get a #SPILL! error.

Nesting too many IFs instead of using IFS or SWITCH

Deep nested IFs like =IF(IF(IF())) are hard to read and debug. Use IFS() for multiple conditions or SWITCH() when matching against specific values.

Not using IFERROR around VLOOKUP in production files

A report full of #N/A errors looks unprofessional. Always wrap lookups: =IFERROR(VLOOKUP(...), "Not Found") for clean output.

🎓 Final Project

Apply all advanced formulas in one real-world project.

Sales Performance Dashboard Using Advanced Formulas

Build a complete Sales Performance Report for 5 regions and 10 sales representatives using only advanced Excel formulas — no Pivot Tables allowed.

Your project must include:

A raw data sheet with 100+ rows: Rep Name, Region, Product, Month, Target, Actual Sales
XLOOKUP to pull each rep's target from a separate targets table
SUMIFS to calculate total actual sales per rep, per region, and per product
COUNTIFS to count deals closed per rep per month
AVERAGEIFS to find average deal size by region
INDEX+MATCH to find the top-performing rep (highest sales)
IFS formula to assign performance tier: Star, Good, Average, Below Target
DATEDIF to calculate each rep's tenure in years and months
FILTER to extract only reps who missed their target this month
IFERROR wrapped on all lookup formulas
A summary dashboard sheet with all KPIs using named ranges

Completion criteria: Your dashboard should be fully dynamic — changing the month filter should update all calculations automatically.

Submit Your Project →

Advanced Excel Formulas