Excel Lookup Deep Dive: VLOOKUP, XLOOKUP, INDEX-MATCH & More
4.6 (156 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
9,425 students enrolled

Excel Lookup Deep Dive: VLOOKUP, XLOOKUP, INDEX-MATCH & More

Master Microsoft Excel VLOOKUP, HLOOKUP, LOOKUP, INDEX-MATCH, XLOOKUP, XMATCH, Two-Dimensional Lookups & Data Validation
4.6 (156 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
9,425 students enrolled
Last updated 6/2020
English
English [Auto]
Current price: $69.99 Original price: $99.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 1.5 hours on-demand video
  • 41 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • How to use Excel lookup functions including VLOOKUP, HLOOKUP, INDEX-MATCH, LOOKUP, XLOOKUP, and XMATCH
  • How to add data validation to make your lookup projects more interactive and easier to use
  • How to correct the errors that most commonly arise when creating lookup formulas
  • How to create and use named ranges in lookup projects
  • How to create nested lookup functions
  • How to perform two-dimensional lookups
Requirements
  • Access to Excel 2010 or later
  • Basic Excel skills (e.g., ability to create basic formulas and use simple functions such as SUM and AVERAGE)
Description

As an Excel user you've probably heard a lot about VLOOKUP and other lookup functions. But what can lookup functions do, why should you use them, and how can they help you?

In essence, lookup functions search through data and return specific values. You may be asking yourself, “Well, why can’t I just do a manual search through my data with ctrl-f and find what I need that way?” And sure, that’s fine if you only need to look up one or two things. But what if you have ten, a hundred, or even thousands of things you have to search for and search through?

That’s where lookup functions come in, they are all about making Excel do the work for you. They can help you to quickly and efficiently retrieve data which saves you time and effort.

This course is a comprehensive deep dive into Excel’s lookup functions including (but certainly not limited to) the ever-popular VLOOKUP, my personal favorite, INDEX-MATCH, and the new function, XLOOKUP. It will give you a solid foundation of lookup function knowledge, but also challenge you to learn about and create nested and two-dimensional lookup functions that can really take your Excel capabilities to the next level.

In this course you'll get:

  • Detailed video walkthroughs so that you can feel confident about each step

  • Copies of all the files used in the course videos so that you can follow along and practice

  • Exercises that will challenge your understanding and deepen your lookup knowledge.

By the end of this course you’ll be able to confidently use lookup functions, perform two-dimensional lookups, create nested lookup functions, use data validation in your lookup projects, and know how to correct common errors, among many other things.

And while I hope that you love the course, Udemy has a 100% 30-day money back policy so you can try the course risk-free and can request a total refund if it's not meeting your expectations.

I'm so excited to teach this course because Excel lookup functions are my absolute favorite. There’s just something about seeing your data magically populate in cells because of a complex formula that you created that’s very satisfying - I can't wait for you to experience this for yourself and start loving lookup functions as much as I do. So check out one of the preview lectures for some free lookup function knowledge and a peek into my teaching style, or if you're ready to learn all you can about lookup functions right now click the "Buy now" button and I'll see you in there!



Who this course is for:
  • The Excel user who wants to be able to confidently use lookup functions
  • The Excel user who wants to gain a deep understanding of how lookup functions work and wants to learn more than just how to use VLOOKUP
  • The Excel user who is looking to enhance their knowledge of Excel and learn lookup concepts that can take their productivity and efficiency to the next level
Course content
Expand all 28 lectures 01:35:22
+ Introduction
2 lectures 04:00

Welcome to the Excel Lookup Functions Deep Dive course! Learn a little about me and what you'll be learning in the course.

Preview 01:47

Learn how you can get the most out of the course and set yourself up for success.

How to Get the Most Out of the Course
02:13
+ Lookup Functions Overview
2 lectures 09:03

Here you'll learn about what lookup functions can do and how they can save you time and effort in your Excel projects.

Preview 04:57

Learn lookup function definitions and how to choose which functions to use for your projects.

Lookup Functions Terminology and Operations
04:06
+ VLOOKUP and HLOOKUP
4 lectures 21:31

Learn how to use the ever-popular VLOOKUP function. We'll go over how to build a VLOOKUP formula step by stem, explain the VLOOKUP syntax, and when to use absolute reference.

Preview 09:18

Learn the difference between exact and approximate matches and how to use the fourth argument of VLOOKUP.

Exact and Approximate Match
04:04

Learn how to perform a horizontal search using the HLOOKUP function.

The HLOOKUP Function
02:24

Learn how to perform a two-dimensional search using an HLOOKUP function nested inside a VLOOKUP function.

The VLOOKUP-HLOOKUP Function (Advanced)
05:45
+ The Table Array Argument
3 lectures 07:55

Learn about when you should use absolute reference in your lookup functions and find out an easy way to add absolute reference to your arrays when you're building your formulas.

Absolute Reference
01:32

Learn about and create named ranges which can simplify your lookup formulas.

Named Ranges
01:47

Learn how to use VLOOKUP and INDIRECT together to dynamically switch between table ranges.

The VLOOKUP-INDIRECT Function (Advanced)
04:36
+ LOOKUP
3 lectures 05:44

Learn about the LOOKUP function in its vector form.

LOOKUP Vector Form
02:56

Learn about the Array Form of the LOOKUP function.

LOOKUP Array Form
02:01

Learn how exact and approximate match work in the LOOKUP function.

Exact and Approximate Match
00:47
+ INDEX and MATCH
5 lectures 19:17

Learn how to use the array and reference forms of the INDEX function.

The INDEX Function
05:37

Learn how to use the MATCH function and about its different match types.

The MATCH Function
01:48

Learn how to use INDEX and MATCH together to create a flexible lookup formula that rivals VLOOKUP.

The INDEX-MATCH Function (Advanced)
05:05

Learn how to create an INDEX-MATCH-MATCH formula so that you perform a two-dimensional lookup.

The INDEX-MATCH-MATCH Function (Advanced)
02:29

Learn how to use VLOOKUP and MATCH together for another way to perform a two-dimensional lookup.

The VLOOKUP-MATCH Function (Advanced)
04:18
+ XLOOKUP and XMATCH
5 lectures 17:30

Learn about the new Excel function, XLOOKUP. Create a dynamic array and learn how to create a custom error message.

The XLOOKUP Function
05:20

Learn about the different match types that you can use in the XLOOKUP function.

Match Mode
05:49

Learn about the different search types that you can use in the XLOOKUP function.

Search Mode
02:43

Create a two-dimensional lookup by using one XLOOKUP function nested inside another.

The XLOOKUP-XLOOKUP Function (Advanced)
02:35

Learn about the new XMATCH function.

The XMATCH Function
01:03
+ Bonus Material
3 lectures 09:32

Learn about the different errors that may arise when using lookup functions and how to correct them.

Common Errors and How to Correct Them
03:15

Learn about Excel wildcards and how to use them in your lookup formulas.

Wildcards
03:07

Learn how to add data validation to your lookup spreadsheets to make them more interactive and user friendly.

Data Validation
03:10
+ Conclusion
1 lecture 00:50

Congratulations on completing the Excel Lookup Functions Deep Dive course!

Conclusion
00:50