The Ultimate Lookup Formulas Course
The Step-by-Step System for Mastering Excel's
Most Important Functions & Formulas
Learning the lookup formulas will change how you work with Excel.
Watch the overview video below to learn more.
VLOOKUP is one of the most highly requested Excel skills by employers.
The lookup functions are the most important functions in Excel because of their power and versatility. Functions like VLOOKUP and INDEX/MATCH allow us to quickly find data on a worksheet and return results.
This is great for everything from a simple list of names to interactive financial models.
If you have spent time manually looking up data in Excel, then you know how painful and frustrating it can be.
Lookup Formulas can save us a lot of time by automating this process, allowing us to use the true power of Excel. They are really great tools that every Excel user can benefit from.
If you have used VLOOKUP or INDEX/MATCH, then you also know that these functions are prone to a lot of errors. They can be very finicky, and we spend a lot of time trying to figure out what is causing the errors.
A big part of our job is to prevent and handle the lookup formula errors to keep our Excel files running smoothly. With great power comes great responsibility… 🙂
My Journey with the Lookup Functions
I have been using Excel for over 10 years in accounting and finance roles. I still remember when I first learned how to write a VLOOKUP formula…
I had a list of transactions and I was looking up the account code in a chart of accounts to return the account description.
Once I wrote the formula I was really excited that it returned the result I was looking for (definitely worth a happy dance)!
"I then copied the formula down about a thousand rows and saw all the results appear instantly in the cells below. AMAZING!!!"
This is when I started to realize how powerful Excel is. I was hooked. Not only did this one VLOOKUP formula save me a ton of time, it also allowed me to take on more exciting projects.
I was relieved that I wouldn't have to spend the entire day looking up account codes. I'm not sure how I would have stayed awake for that boring task. 🙂
The Lookup Functions Change How You Work With Excel
Since that day, I have written A LOT of lookup formulas. Whether it's VLOOKUP, INDEX/MATCH, COUNTIFS, GETPIVOTDATA, or one of the many other lookup functions, I have spent a lot of time learning these formulas.
They are critical for our jobs for a lot of reasons. Not only do they save us time, but the lookup functions also allow us to create relationships between data sets.
We can also use lookup formulas in place of complex nested IF formulas. This helps us reduce errors and can really improve the efficiency of our Excel files.
There are probably an infinite number of uses for the lookup functions. Their power and versatility make them a critical part of any financial model.
Lookup formulas are extremely important for creating reports and analyzing data. Organizations around the world rely on employees with these skills so they can understand their business and make better decisions.
So, I have created The Ultimate Lookup Formulas Course to help you master these skills and advance in your career.
Who Is This Course For?
The Ultimate Lookup Formulas Course has been designed to help you learn in an easy, step-by-step manner . This course will equip you with the Excel skills that employers are looking for.
Whether you are complete beginner, or have been using the lookup functions for some time, I have designed this course to help you take your skills to the next level.
This course is for you if you answer yes to any of the following questions:
- Are you in an accounting, finance, engineering, marketing, HR, development, admin or analyst role?
- Do you use Excel frequently?
- Do you work with lists or sets of data in Excel?
- Do you spend time trying to lookup or search for information between data sets?
- Are you using the lookup formulas now, but frustrated with the amount of errors they can return?
- Do you spend time trying to figure out what is causing errors with your formulas?
- Do you want to build more complex financial models, reports, and dashboards that will impress your boss and put you in high demand?
The Ultimate Lookup Formulas course will teach you the tools and techniques to help get your job done faster. You will be able to take control of your spreadsheets and harness the true power of Excel.
What Others Are
Saying About The Course
"I only have the following words to say about this course: TERRIFIC! or AWESOME! or SUPERB! I got caught by the course and went through all the modules in two days. Very easy to follow and your job given all the teaching was really amazing. Even though I believed I had a good knowledge on all these concepts, I still learned several new things while going through it. The last two modules are fantastic!"
"Thank you for reminding me how much money I spend at Starbucks every day. Ha! But seriously, the videos are really good and easy to follow. Also, having a quiz at the end of each module is a nice addition to the course."
"I completed module 4 today – that was nicely presented, and for someone not used to dealing with errors, could be worth the cost of the course right there."
"The videos are easy to understand and the pace is good."
How is the Course Structured?
The Ultimate Lookup Formulas Course is an online video course. This means you can watch the screencast videos any time you want, in the comfort of your own home or office.
- The Ultimate Lookup Formulas Course is is divided into 8 training modules.
- Each of these modules is comprised of about 7-10 short videos.
- Each video is about 5-10 minutes in length. The short videos allow you to learn the concepts one at a time, and give you a chance to practice the techniques.
- Each module contains a quiz at the end to help test your knowledge and make sure you understand the concepts.
The course has been designed to help you learn in a simple step-by-step process. You will be able to apply what you are learning and see immediate results.
The short videos also make it easy to come back and re-watch a video on a specific topic to get a refresher. Plus, the course software tracks your progress to keep you organized.
What Am I Going to Learn?
Here is a detailed list of
The 8 Traning Modules
Module 1: Getting Started with VLOOKUP
Videos: 13 | Total Duration: 61 min
|1. Overview of the VLOOKUP Function (3:24)|
|2. Writing Your First VLOOKUP Formula (6:31)|
|3. VLOOKUP Stops at First Match for Exact Match (1:19)|
|4. Sort Ascending Order Not Required for Exact Match VLOOKUP (1:55)|
|5. Absolute References in VLOOKUP Formulas (6:11)|
|6. VLOOKUP Column Index is Relative to Table Array Range (1:10)|
|7. VLOOKUP Numbers with Exact Match Plus Shortcuts (3:36)|
|8. Keyboard Shortcuts for Typing VLOOKUP Formulas (2:00)|
|9. VLOOKUP To Other Sheets in the Workbook Plus GoTo Tip (4:23)|
|10. Find an Approximate or Closest Match with VLOOKUP (6:42)|
|11. Calculate Commission Rates with VLOOKUP Approximate Match (5:24)|
|12. Quiz Review Module 1 (4:32)|
|13. Homework Review Module 1 (14:08)|
In module 1 we take a trip to Starbucks to learn the essentials of the VLOOKUP function. This will give you a good understanding of how VLOOKUP works, and what you can use it for.
You will learn:
- How to write your first VLOOKUP formula.
- Key concepts that will help prevent formula errors.
- How to use VLOOKUP to lookup numbers.
- How to use VLOOKUP for an approximate match for commission and tax rate calculations.
By the end of this module you will be writing your own VLOOKUP formulas with ease.
Module 2: Dynamic VLOOKUP Formulas
Videos: 9 | Total Duration: 43 min
|1. Cell References for Column Index Number in VLOOKUP (3:45)|
|2. COLUMN Function for Dynamic Column Index Numbers (4:44)|
|3. The MATCH Function Explained (5:45)|
|4. Dynamic VLOOKUP Formulas with MATCH (6:04)|
|5. Price Calculator with VLOOKUP and MATCH (5:00)|
|6. Writing Efficient Formulas with Helper Cells (7:10)|
|7. Move Lookup Table to Another Sheet with Cut-Paste (1:42)|
|8. Quiz Review Module 2 (2:40)|
|9. Homework Review Module 2 (6:34)|
In module 2 we learn how to make our VLOOKUP formulas more flexible and dynamic. This helps prevent formula errors when changes are made to the spreadsheet. These techniques can also be used to create interactive reports. You will learn:
- How to use formulas for the column index number to prevent errors.
- How the MATCH function works.
- To use the MATCH function with VLOOKUP to create dynamic lookup formulas.
- How to create interactive price calculators with drop-down lists.
This module will equip you with the skills to create interactive reports that your boss and users will love.
Be prepared to get questions like, "hey, how did you do that in your Excel file???"
Module 3: INDEX & MATCH - A Powerful Alternative to VLOOKUP
Videos: 9 | Total Duration: 46 min
|1. Overview and Benefits of the INDEX Function (4:14)|
|2. How to Use the INDEX Function (4:04)|
|3. Dynamic Lookup for Rows with INDEX and MATCH (5:39)|
|4. Dynamic Lookup for Rows and Columns with INDEX and MATCH (4:27)|
|5. Why Use INDEX MATCH Instead of VLOOKUP (5:57)|
|6. How To Quickly Write an INDEX MATCH (Vlookup Replacment) Formula (3:34)|
|7. Lookup to the Left with INDEX and MATCH (3:21)|
|8. Quiz Review Module 3 (3:41)|
|9. Homework Review Module 3 (9:03)|
Module 3 is all about INDEX & MATCH. This combination of functions can be used to create lookup formulas that have some great benefits over VLOOKUP.
- An overview of the INDEX function.
- Create dynamic lookup formulas for rows and columns.
- How to quickly write INDEX & MATCH formulas instead of VLOOKUP.
- How to lookup to the left.
INDEX & MATCH is a great alternative to VLOOKUP. This module will not only equip you with the right skills, it will also teach you how to explain the formula to your co-workers and boss. This is critical to creating spreadsheets that others can use and understand.
Module 4: Handling Formula Errors Like a Pro
Videos: 12 | Total Duration: 59 min
|1. Common Errors and Causes with Lookup Formulas (2:13)|
|2. Common N/A Errors - Lookup Value Does Not Exist (4:08)|
|3. Causes of N/A Errors - Number Stored As Text (5:24)|
|4. 3 Ways to Convert Numbers to Text (6:12)|
|5. N/A Errors - TRIM Blank Spaces (3:00)|
|6. N/A Errors - Find and Replace Blank Spaces (2:35)|
|7. N/A Errors - Non Printable Characters (5:17)|
|8. Causes of Other Common Errors - REF NAME VALUE (4:56)|
|9. The IFERROR Function Explained (6:31)|
|10. The ISERROR Function Explained (3:31)|
|11. Quiz Review Module 4 (4:45)|
|12. Homework Review Module 4 (13:10)|
Lookup formulas can produce errors, a lot of errors! It is critical that you know how to prevent and handle these errors. This will keep your Excel files looking nicer and calculating faster.
In this module we take a deep dive on all these errors and how to handle them like a pro.
- Common formula errors and what causes them.
- Causes and solutions for the #N/A error (the most common lookup formula error).
- How to handle other error types like #REF, #VALUE, #NAME (less common but still very important).
- How to use the IFERROR function to return a different value when an error occurs (make your spreadsheet look nice and calculate faster).
Formula errors can be one of the most frustrating and time consuming parts of creating reports and financial models. This module provides in depth training on error handling techniques that will save you a lot of time.
Just be prepared to get calls from co-workers asking for help. These are skills that will really put you in high demand... 🙂
"Module 5 is another ‘hidden gem’ in the course in my opinion – who would expect to find a section on Tables in a course on lookup formulas?"
Module 5: Save Time with Excel Tables
Videos: 14 | Total Duration: 76 min
|1. Overview of Excel Tables - Pros and Cons (4:58)|
|2. Getting Started with Excel Tables (4:30)|
|3. Excel Table Options and Features (8:10)|
|4. Getting Started with Table Formulas Part 1 (5:13)|
|5. Getting Started with Table Formulas Part 2 - Standard vs Structured Refs (4:27)|
|6. Getting Started with Table Formulas Part 3 - Formulas on Other Sheets (3:00)|
|7. Writing Table Formulas Part 1 (5:11)|
|8. Writing Table Formulas Part 2 (5:19)|
|9. Quick Tip for Writing VLOOKUPS with Excel Tables (2:17)|
|10. How to Write INDEX MATCH Formulas with Tables (3:54)|
|11. How to Use the MATCH Function for Table Column References (5:42)|
|12. How to Use Structured Referencing in VLOOKUP Formulas (5:56)|
|13. Quiz Review Module 5 (5:36)|
|14. Homework Review Module 5 (11:30)|
Excel Tables are an amazing feature of Excel that can save you a lot of time when working with data. They allow you to quickly sort, filter, and format ranges of data. They also have those weird looking formulas called structured references.
In this module you will learn how to use Excel Tables to your advantage, and how to write the structured reference formulas. These formulas are actually much faster and easier to read and write. You will find a lot of benefits once you learn how to use them.
- The Pros and Cons of Excel Tables.
- How to read and write Table formulas (aka structured reference formulas)
- The advantages of using Tables for lookup formulas.
- How to write VLOOKUP and INDEX MATCH formulas with Tables.
Learning Excel Tables will also help prepare you for some of the new data tools in Excel like Power Query and Power Pivot.
Module 6: How to Create Lookup Tables
Videos: 9 | Total Duration: 54 min
|1. Overview of Lookup Tables (3:06)|
|2. Create Lookups Tables with the Remove Duplicates Feature (5:25)|
|3. Create a Lookup Table using a Pivot Table (3:37)|
|4. Create a Named Range for a Lookup Table (4:55)|
|5. How to use Dynamic Named Ranges with Lookup Tables (9:56)|
|6. How to Fix External Link References When Copying Formulas to New Workbooks (3:32)|
|7. How to Fix External Link References by Changing the Source Workbook (4:25)|
|8. Quiz Review Module 6 (3:20)|
|9. Homework Review Module 6 (10:53)|
If you work with data from multiple sources, then you probably spend time creating lookups between the data sets. You might have to modify the data to exclude unneeded columns or duplicate rows.
- Overview and benefits of lookup tables.
- The Remove Duplicates feature of Excel.
- How to use a pivot table to create a lookup table.
- How to create dynamic named ranges to include new data automatically.
In this module we look at some techniques for creating lookup tables. This makes it easy to pull-in information and create relationships between your data sets. You will even learn how to use pivot tables and dynamic named ranges to help automate this process.
Module 7: Advanced Lookup Formulas
Videos: 11 | Total Duration: 59 min
|1. Lookup with Mulitple Criteria (6:00)|
|2. Lookup with Mulitple Criteria using Ampersand (1:43)|
|3. Overview of Dates in Excel (3:17)|
|4. Vlookup with Dates Part 1 (2:54)|
|5. Vlookup with Dates Part 2 - Round Down (4:37)|
|6. Vlookup with Dates Part 3 - Date Function (5:05)|
|7. Lookup 2nd 3rd Nth Place with LARGE Function (5:32)|
|8. Lookups with Wildcards (8:35)|
|9. Lookup Bottom Performers with SMALL (4:15)|
|10. Quiz Review Module 7 (3:45)|
|11. Homework Review Module 7 (15:49)|
In this module we learn more advanced techniques for VLOOKUP and INDEX MATCH.
You will surely find yourself in situations where you need to lookup dates or partial matches, and this module will help with those problems.
- Lookups with multiple criteria (columns)
- Lookup formulas for dates
- How to return the 2nd, 3rd, Nth place from a list.
- Lookups with wildcards and partial matches.
In this module I also explain how dates are stored in Excel, and how the date system works. This will really help you with all aspects of working with dates and times in Excel.
Module 8: Alternative Lookup Functions
Videos: 13 | Total Duration: 68 min
|1. Overview of Alternative Lookups - Plus Conditional Formatting Bonus Tip (2:19)|
|2. HLOOKUP - The Horizontal VLOOKUP (4:16)|
|3. LOOKUP Function - How to Calculate Fiscal Quarters (5:52)|
|4. COUNTIF Function - Check if a Value Exists (2:28)|
|5. COUNTIFS Function - Check if a Value Exists based on Multiple Criteria' (6:14)|
|6. SUMIF Function - Lookup a Value and Return a Number (3:21)|
|7. SUMIFS Function - Lookup Multiple Criteria and Return a Number (4:00)|
|8. CHOOSE - Alternative to Nested IFs Formulas (4:28)|
|9. INDIRECT Function to Create a Summary Sheet (6:31)|
|10. Compare Multiple Lists of Data with a Pivot Table (5:20)|
|11. GETPIVOTDATA to Create Customized Reports (8:09)|
|12. Quiz Review Module 8 (4:01)|
|13. Homework Review Module 8 (11:49)|
VLOOKUP and INDEX MATCH aren't the only players in the game. There are a lot of other lookup formulas that you can add to your toolbox for various jobs.
In this module we look at other common lookup formulas, and when to use them. These functions can save you a lot of time, and make you look like a pro.
- How to calculate fiscal quarters with the LOOKUP function.
- COUNTIF and COUNTIFS to check if a value exists.
- SUMIF(S) to return numbers.
- INDIRECT to create a summary sheet of all sheets in the workbook.
- Quickly compare multiple lists with a Pivot Table.
- Create customized reports with GETPIVOTDATA.
- And much more…
This is a toolbox that you can fill your resume with. You will learn a lot of new ways to work with and analyze data.
Some Awesome *Bonuses*
Just For You!
Bonus #1: Excel Companion Files
Along with the videos you are also getting the Excel files I use throughout the course. These files make it easy to follow along with the videos.
This also includes the cheat sheets and guides I use to make it easy to remember the techniques. Here is an example of a quick guide that will help you remember how to write INDEX MATCH formulas to replace VLOOKUP. As simple as 1-2-3.
Print them out and hang them on your wall...
Bonus #2: Quizzes & Homework
Each module contains a quiz and homework assignment. There are additional videos where I review the quiz and homework to make sure you fully understand the material.
Bonus #3: Comments Section on Every Lesson Page
Each page in the course has a comments section. You can ask questions here and also see what other students in the community are asking. I will be here answering your questions and helping you along the way.
Bonus #4: The Best Course Software
Click the video below to get a sneak peak of the course.
Click here if you have trouble viewing the video
The course software is very easy to use. It tracks your progress for you, allowing you to come back right where you left off. This keeps you organized and helps you focus your time on learning.
You can access the course anytime, from any device. Watch the videos on any tablet or smartphone.
Bonus #5: Training Module on Power Query and Power Pivot
Videos: 9 | Total Duration: 69 min
|1. Overview of Power Query and Power Pivot (4:51)|
|2. The Merge Feature in Power Query (10:53)|
|3. Summarize Power Query Output with a Pivot Table (3:43)|
|4. How to Update the Query with New Data (4:00)|
|5. How to Build an Automated Reporting System with Power Query (11:18)|
|6. How to Handle Errors with Power Query Merges (9:40)|
|7. Add Additional Merges to a Power Query Table (4:39)|
|8. How to Merge Multiple Fields in Power Query (11:03)|
|9. How to Create Relationships and Reports with Power Pivot (8:44)|
|10. Quiz Review Module 1 (4:32)|
|11. Homework Review Module 1 (14:08)|
ork Review Module 4 (13:10)
We often use lookup formulas like VLOOKUP to create relationships between data sets.
New business intelligence tools from Microsoft like Power Query and Power Pivot can also help us create these relationships. These tools are really great for creating automated systems for updating, modifying, and reporting on your data. They allow you to merge or join data sets together to create relationships that make for quicker and easier analysis.
You can think of lookup formulas like VLOOKUP as the trusty old truck that is dependable, and most people are familiar with driving. The new tools like Power Pivot and Power Query are the sports car; modern, sleek, and extremely powerful once you understand how to use them.
In this bonus module I explain how to use both Power Query and Power Pivot to:
- Create relationships between your data sets.
- Use pivot tables to create quick summary reports and analysis.
- Use Power Query to create a system that automatically updates your lookup tables and reports.
This is a great introductory to what these new business intelligence tools are capable of in Excel. These new skills will definitely be in high demand by employers in the future.
Hi, my name is Jon Acampora and I am your instructor for this course.
I have been using Excel for over 10 years as financial analyst and systems developer. Over that time I have become passionate about learning Excel, and finding the fastest and most efficient ways to accomplish a task.
My interests with Excel cover a broad range of topics from data visualization to application development. I have developed dozens (if not hundreds) of VBA applications and add-ins.
I have been awarded the Microsoft MVP Award for my contributions to the Excel community and helping others learn.
More than anything, I really enjoy teaching you these skills and techniques to help you work efficiently and get your job done faster! I am always happy to help answer questions, and I'm here to support you on your journey to learn Excel (and impress your boss). 🙂
Frequently Asked Questions
When does the course start and finish?
The course starts now and never ends! It is a completely self-paced online course - you decide when you start and when you finish.
How long do I have access to the course?
How does lifetime access sound? After enrolling, you have unlimited access to this course for as long as you like - across any and all devices you own.
How long does it take to complete the course?
This depends on how much time you have to spend each day on watching the videos. I recommend watching one or two videos a day, and then practicing the techniques in your job. If you complete one module per week then it will take 8 weeks to complete the course.
Can I download the Excel files used in the course?
Yes! You will be able to download all the files I use throughout the course. This makes it easy to follow along and test the code.
Can I watch the videos on my iPad or tablet?
Yes, the course can be accessed on any tablet or smart phone, and it looks beautiful. You can watch the course on your iPad while following along with the Excel files on your computer screen.
What version of Excel do I need?
You can use any version of Excel for either Windows or Mac. I use Excel 2013/2016 for Windows in most of the videos in the course. All the functions, formulas, and techniques explained in the course can be used on either the Windows or Mac versions of Excel, from 2003 and beyond. The bonus module on Power Pivot and Power Query will only apply to Windows versions of Excel from 2010 and beyond.
How do I ask questions and get support?
Each video lecture page has a comments section at the bottom. You can use this section to ask questions and see what questions other students have. I will be there helping to answer your questions.
What payment methods do you accept?
All major credit cards including Visa, MasterCard, and American Express are accepted. You can also pay with PayPal.
Can my employer pay for my enrollment?
Yes, you can use your company's credit card to pay for the course. A receipt will also be emailed to you that you can submit for reimbursement to your employer. Most companies are willing to invest in making their employees even more awesome! 🙂
Can I purchase multiple memberships for my employees?
Yes, there is a team package available at a discounted price. This package includes 3 memberships to the course for you and your team members. Each team member will have their own account and be able to track their progress in the course. Please contact me (firstname.lastname@example.org) if you would like to register more than 3 members.
What if I am unhappy with the course?
If you are unsatisfied with your purchase, contact me within 30 days of purchase to get a full refund. I want you to be fully satisfied and learn a lot!
Join This Awesome Group
This dynamic price calculator lesson alone shows what a great deal this course is and the benefits of using the knowledge in here. This alone will save me many hours of work!
I had difficulty understanding Index & Match, despite reading several Excel books and also Excel video guides by other Excel gurus. But just after going through your video, I now have a much better understanding of how Index & Match works. (You have a gift of breaking complex Excel knowledge into small manageable bits!)
This [module 1] is an awesome tutorial module Jon! I feel much more confident with the VLOOKUP function now! 🙂