Advanced Excel · Beginner level

Beginner level · Module 2 of 5 · 11 guided hours

Lookup and Logical Functions

Combine data from different tables with lookups, and answer business questions with logical and conditional aggregation functions.

Learning objectives

  • Look up values with XLOOKUP and INDEX/MATCH
  • Build decisions with IFS, AND, OR and conditional totals with SUMIFS and COUNTIFS
  • Clean and transform text and dates with functions

Topics

  • XLOOKUP
  • INDEX and MATCH
  • approximate-match lookups
  • IFS, AND, OR, SWITCH
  • SUMIFS, COUNTIFS, AVERAGEIFS
  • text and date functions

Lessons

  1. Lesson 1 XLOOKUP and INDEX/MATCH

    Find values in other tables by a key, handle missing keys and look up by ranges.

    lookup keyXLOOKUPINDEX/MATCHapproximate match for bands

  2. Lesson 2 Logical Functions and Conditional Totals

    Express business rules with logical functions and calculate totals and counts that meet conditions.

    IFSAND / ORSUMIFSCOUNTIFS

  3. Lesson 3 Text and Date Functions

    Clean text and work with dates using functions, so data from different sources can be combined.

    TRIM, PROPER, UPPERTEXTSPLIT / LEFT / MIDTEXT and DATEEOMONTH and NETWORKDAYS

Practical activity

Fill an order sheet with product names and prices from a product table, apply discount rules and summarise sales by branch and month with SUMIFS.

Expected outcomes

  • Workbooks that combine related tables
  • Business rules expressed as clear formulas

Assessment

Practical test of ten formula tasks marked by expected results, including missing-value cases.

Free taster lesson Core Functions for Real Work Read a free self-study lesson on this topic in the Free Course Library.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.