Lookup functions mastery

Beginner 3 hr 16 exercises

Progress from basic VLOOKUP patterns to more flexible INDEX/MATCH and XLOOKUP workflows.

What you'll learn

Write VLOOKUP and HLOOKUP formulas with the correct column or row index and match mode
Choose between exact match and approximate (tiered) match for rate tables and bracket lookups
Replace VLOOKUP with XLOOKUP for cleaner left-side lookups, default values, and wildcard searches
Build INDEX+MATCH formulas to look up data when the key isn't in the left-most column
Match against two criteria at once using INDEX with paired MATCH calls or nested XLOOKUP
Handle missing lookups gracefully with IFNA and XLOOKUP's not_found argument

Functions you'll practice

HLOOKUP IFNA INDEX MATCH VLOOKUP XLOOKUP

Exercises in this track

0 of 16 completed 0%

1

VLOOKUP for price

Beginner

Find a product's price from a product catalog using the product code.

2

Use VLOOKUP to find an employee’s department

Beginner

Look up an employee's department using their employee ID.

3

HLOOKUP for quarterly targets

Beginner

Use HLOOKUP to retrieve a sales target for a specific quarter from a horizontal table.

4

VLOOKUP with approximate match

Intermediate

Calculate shipping rate based on package weight using a tiered rate table.

5

Find a customer's city with VLOOKUP

Beginner

Look up which city a customer is located in using their customer ID and a customer directory table.

6

Look up a product with XLOOKUP

Beginner

Use XLOOKUP to find a product's category from a product catalog.

7

XLOOKUP with exact match

Beginner

Use XLOOKUP to pull current stock quantities for items on a reorder list.

8

XLOOKUP left lookup

Intermediate

Return an ID from a column to the left of the lookup value using XLOOKUP.

9

XLOOKUP with default

Intermediate

Look up product info, returning "Discontinued" if product not found.

10

XLOOKUP with wildcard search

Intermediate

Use XLOOKUP with wildcard matching to find a product and price when you only know part of the product name.

11

Lookup error handling

Beginner

Handle missing lookups with IFNA for VLOOKUP and the not_found argument in XLOOKUP.

VLOOKUP XLOOKUP IFNA

12

Look up a manager with INDEX+MATCH

Intermediate

Look up an employee's manager using INDEX-MATCH (lookup value not in first column).

13

INDEX-MATCH left lookup

Intermediate

Find a customer's email address where email is to the LEFT of customer ID.

14

INDEX-MATCH with MATCH in both directions

Intermediate

Use INDEX with two MATCH functions to return a value from a shipping rate grid.

15

XLOOKUP multiple criteria

Advanced

Find the price for a specific product in a specific quarter using nested XLOOKUP.

16

Return multiple columns

Advanced

Look up a customer ID and return name, email, and phone in one formula row.

Where to go next

Pick another track

Browse all learning tracks to find your next focus area.

Browse all tracks