Chapter 2 · 8 levels

Decisions and lookups

Turn business rules into formulas with IFS, AND, OR and NOT, and bring data from one table into another with VLOOKUP, HLOOKUP, XLOOKUP and INDEX/MATCH.

Start

Most spreadsheet work is one of two things. Either a rule decides something per row (which shipping band, which customers get a note, which prices need new shelf cards), or a value has to come from another table (the product name for an id, today’s price for a line, last year’s average for a quarter). This chapter covers both.

You start with IFS, the cleaner way to write a rule with several bands, and with AND, OR and NOT for rules with more than one condition. Then you look things up: VLOOKUP with an exact match and IFERROR for ids that aren’t there, HLOOKUP for tables laid out sideways, XLOOKUP for a catalog whose id sits in the last column, and INDEX with MATCH, the classic pair that looks in any direction, compared side by side with the other two. A short quiz checks what each lookup returns when a key is missing. The boss level applies a real price change to the Harbor & Pine kitchen range in three steps.

Every formula here works the same way in Microsoft Excel and Google Sheets. IFS needs Excel 2019 or later and XLOOKUP Excel 2021 or Microsoft 365; Google Sheets has both.

Levels

  1. Three shipping bands with IFS

    from Megan Collins, Head of Sales

    ifs, nested if, comparison operators

    Code

  2. Thank-you notes with AND and OR

    from Anna Chen, Sales Manager

    and, or, not, if, logical functions

    Code

  3. List prices with VLOOKUP and IFERROR

    from Mei Morgan, Buyer

    vlookup, exact match, iferror, ifna, lookups

    Code

  4. Last year's quarter averages with HLOOKUP

    from Jacob Long, Account Executive

    hlookup, lookups, exact match, if

    Code

  5. Product names with XLOOKUP

    from Thomas Whitaker, Head of Merchandising

    xlookup, lookups, if not found, absolute references

    Code

  6. INDEX/MATCH, right to left

    from Shirley Chen, Buyer

    index match, match, exact match, lookups, vlookup

    Code

  7. What a lookup returns for a missing key

    from Jerry Smith, Sales Associate

    xlookup, vlookup, index match, if not found, n a error

    Quiz

  8. Boss: update the kitchen price list

    from Rachel Ramirez, Merchandise Planner

    xlookup, if not found, percent change, ifs, absolute references

    Boss · 3 steps

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.