Lookups
Bring values over from another table with VLOOKUP, XLOOKUP and INDEX/MATCH, and fix #N/A.
Lessons
In the order the Roadmap teaches them. The free one needs no sign-up.
Free · Confident · 11 min
Look up a price with VLOOKUP and XLOOKUP
Bring each order's price over from a price list with VLOOKUP or XLOOKUP, and avoid the missing-FALSE trap.
Start the lessonIn the full course. Confident · 12 min
Why VLOOKUP returns #N/A
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 lessonIn the full course. Confident · 8 min
HLOOKUP for sideways data
Look up a value in a table whose labels run across the top row, such as months, with HLOOKUP and a row number.
See the lessonIn the full course. Confident · 10 min
INDEX and MATCH
Look up a value from any column, even one to the left of the column you search, with INDEX and MATCH.
See the lessonIn the full course. Confident · 10 min
XLOOKUP, the modern lookup
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 lessonIn the full course. Confident · 10 min
XLOOKUP with a default when nothing is found
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 lessonIn the full course. Confident · 8 min
Look up across sheets
Write a lookup whose table sits on another sheet, with the sheet name, quotes, $ signs and room for new rows right.
See the lessonIn the full course. Advanced · 11 min
Lookups with two conditions
Look up a value that depends on two columns, with a helper key column or with SUMIFS when the answer is a number.
See the lessonIn the full course. Advanced · 9 min
Return several columns at once
Bring back several columns for an item at once with one XLOOKUP, and fill it down to look up a whole list.
See the lessonIn the full course. Advanced · 10 min
Approximate match: tax brackets and commission tiers
Look up which tier or bracket a number falls in with an approximate-match VLOOKUP or XLOOKUP on a sorted table.
See the lessonIn the full course. Advanced · 9 min
Find the last matching value
Return the most recent entry for an item in a log with XLOOKUP's search mode -1, which searches from the bottom up.
See the lessonIn the full course. Advanced · 11 min
XMATCH
Find the position of a value with XMATCH, including the next larger value and the last match, and turn it into a result with INDEX.
See the lessonPaths with these lessons
A path is a short list of lessons from the course, picked for one goal.
Lookups without the #N/A
For matching lists by hand today: prices, product names, staff details, commission tiers.
9 lessons · about 1 h 30 min · 2 free · Beginner to Advanced
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 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 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 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.