StretchLearn Course

Build spreadsheets that do not break — and update themselves

Trade fragile VLOOKUPs and copy-paste reports for modern Excel: XLOOKUP and INDEX/MATCH lookups that survive edits, IF and IFS logic that handles real rules, SUMPRODUCT for weighted math, and FILTER, SORT, and UNIQUE that spill live results.

Preview Curriculum Back to Catalog

Beginner9 hr 25 minSelf PacedRegistered

Course Overview

What this course is designed to develop

This course teaches anyone who lives in spreadsheets to replace fragile, hard-coded formulas with robust ones that survive edits, scale to thousands of rows, and update themselves. You will build exact and approximate lookups with XLOOKUP, recreate them with INDEX/MATCH for older Excel, wrap logic in IF, IFS, AND, OR, and IFERROR, run weighted averages and multi-condition counts with SUMPRODUCT, and assemble live reports with FILTER, SORT, SORTBY, UNIQUE, and SEQUENCE. Each lesson works from a concrete dataset such as a sales export or price list, shows the exact formula, and explains the gotchas so your spreadsheets stop breaking.

Learning Outcomes

What the learner should be able to understand, build, or execute.

Write XLOOKUP formulas with exact match, approximate match, if_not_found, and left-lookup to replace VLOOKUP entirely

Build INDEX/MATCH lookups that keep working when columns are inserted, deleted, or reordered

Construct conditional logic using nested IF, IFS, AND, OR, and IFERROR to handle real decision rules and errors

Calculate weighted averages and multi-criteria counts and sums with SUMPRODUCT without array-entering

Create self-updating reports using the dynamic array functions FILTER, SORT, SORTBY, UNIQUE, and SEQUENCE

Diagnose and fix the common formula errors #N/A, #REF!, #VALUE!, #SPILL!, and circular references

Curriculum Preview

Inside the curriculum: a structured path from fundamentals to execution.

Preview the course structure, see how the modules build on one another, and understand the path this program is designed to take you through.

Module 1

Module 1: Lookups That Do Not Break: XLOOKUP and INDEX/MATCH

Replace VLOOKUP with XLOOKUP for clean, error-handled lookups, and learn INDEX/MATCH so your formulas keep working when columns move. You leave able to look up any value in any direction, with a sensible fallback when there is no match.

3 lessons

Why VLOOKUP Fails and What Replaces ItContent · 45 min

Preview Enabled

XLOOKUP From Exact Match to FallbacksContent · 50 min

LMS Access

INDEX/MATCH for Every Version and Two-Way LookupsContent · 50 min

LMS Access

Module 2

Module 2: Conditional Logic: IF, IFS, and Error Handling

Turn business rules into formulas with IF, nested IF, IFS, AND, and OR, then make them resilient with IFERROR and IFNA so your sheets stay clean when data is missing or malformed.

3 lessons

IF, Nested IF, and When to Switch to IFSContent · 45 min

LMS Access

Combining Conditions with AND, OR, and NOTContent · 45 min

LMS Access

Catching Errors with IFERROR and IFNAContent · 45 min

LMS Access

Module 3

Module 3: SUMPRODUCT and Multi-Criteria Math

Use SUMPRODUCT to multiply and sum arrays in one step, run weighted averages, and count or sum with several conditions, alongside the simpler COUNTIFS and SUMIFS for everyday cases.

3 lessons

How SUMPRODUCT Multiplies and Sums ArraysContent · 45 min

LMS Access

Multi-Criteria Counting and SummingContent · 50 min

LMS Access

Choosing Between SUMPRODUCT, SUMIFS, and Helper ColumnsContent · 45 min

LMS Access

Module 4

Module 4: Dynamic Arrays: FILTER, SORT, UNIQUE, and Live Reports

Use the spilling functions in Microsoft 365 and Excel 2021 to build reports that update themselves: FILTER to extract matching rows, SORT and SORTBY to order them, UNIQUE to list distinct values, and SEQUENCE to generate numbers, plus how to fix #SPILL!.

3 lessons

FILTER: Extract Rows That Update ThemselvesContent · 50 min

LMS Access

SORT, SORTBY, and UNIQUE for Clean SummariesContent · 50 min

LMS Access

SEQUENCE, Spill References, and Fixing #SPILL!Content · 45 min

LMS Access

Built for Application

A complete learning path, not a one-off inspiration hit.

This program is designed around progression: focused lessons, structured modules, applied resources, assessments, and a course rhythm that turns information into usable capability.

ExcelXLOOKUPINDEX MATCHdynamic arraysFILTER functionSUMPRODUCTspreadsheet formulasdata analysis