For matching lists by hand today: prices, product names, staff details, commission tiers.
Lookups without the #N/A
Bring values over from another table with VLOOKUP, XLOOKUP and INDEX/MATCH, across sheets and by tiers, and find out why a lookup shows #N/A.
The first lesson is free and needs no sign-up.
Do this path on your computer.
Its lessons run in your browser on a laptop or desktop, and every step is checked as you go. Send yourself the link. Nothing is saved until you sign in.
The lessons, in order
Each one takes about 10 minutes. Two are free; the rest are in the full course.
- 1
Why your formula broke when you copied it
Free · Beginner · 7 min
Lock a cell with $ so a formula keeps working when you copy it down.
Start the lesson - 2
Look up a price with VLOOKUP and XLOOKUP
Free · Confident · 11 min
Bring each order's price over from a price list with VLOOKUP or XLOOKUP, and avoid the missing-FALSE trap.
Start the lesson - 3
Why VLOOKUP returns #N/A
In the full course. Confident · 12 min
Tell which of the four causes is behind each #N/A from VLOOKUP, fix it, and hide only the misses that are real.
See the lesson - 4
XLOOKUP, the modern lookup
In the full course. Confident · 10 min
Look up a value in any column and return any other column with XLOOKUP, including columns to the left, with your own result when nothing is found.
See the lesson - 5
XLOOKUP with a default when nothing is found
In the full course. Confident · 10 min
Give XLOOKUP a value to show when nothing matches, and choose the right one for the job, 0, a message or a blank.
See the lesson - 6
Hide errors with IFERROR and IFNA
In the full course. Confident · 11 min
Replace expected errors such as #DIV/0! with a clear value using IFERROR, and use IFNA for lookups so real mistakes still show.
See the lesson - 7
Look up across sheets
In the full course. Confident · 8 min
Write a lookup whose table sits on another sheet, with the sheet name, quotes, $ signs and room for new rows right.
See the lesson - 8
INDEX and MATCH
In the full course. Confident · 10 min
Look up a value from any column, even one to the left of the column you search, with INDEX and MATCH.
See the lesson - 9
Approximate match: tax brackets and commission tiers
In the full course. Advanced · 10 min
Look up which tier or bracket a number falls in with an approximate-match VLOOKUP or XLOOKUP on a sorted table.
See the lesson
Other paths in the Google Sheets course
Google Sheets basics in a weekend
For starting from zero, or only typing numbers into cells today.
14 lessons · about 2 h · 4 free · Beginner to Confident
See the pathReports that don't break
For a weekly or monthly report other people rely on.
11 lessons · about 1 h 45 min · 2 free · Beginner to Confident
See the pathGoogle Sheets for small business
For quotes, stock and money kept in a spreadsheet.
12 lessons · about 2 h 15 min · 3 free · Confident
See the pathPivot tables by Monday
For summarizing a sales or expense list for a meeting.
7 lessons · about 1 h 5 min · 1 free · Confident
See the pathClean up messy data
For exports, CSVs and form responses that need tidying before anyone can use them.
10 lessons · about 1 h 30 min · 1 free · Beginner to Confident
See the pathGet the full course
Every lesson in the Google Sheets course, with new lessons added as they're written.
- All 215 Google Sheets lessons open today, and every new one as it's written.
- The Roadmap, Skills and all seven paths.
- A cheat sheet for every lesson you finish.
- At checkout, add the Excel course for US$49.