Learning the lookup formulas will change how you work with Excel.
Watch the overview video below to learn more.
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… 🙂
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. 🙂
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.
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:
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.
"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."
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 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.
The video below contains a quick overview of the course software and I explain how it is your personal learning assistant.
Click the video below to watch the preview
Click here if you have trouble viewing the video.
The course covers a wide array of lookup formulas that will help you analyze and work with data in new ways. The lessons come with companion Excel files so you can follow along with the videos and practice the techniques.
|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:
By the end of this module you will be writing your own VLOOKUP formulas with ease.
|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:
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???"
|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.
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.
|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.
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?"
|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.
Learning Excel Tables will also help prepare you for some of the new data tools in Excel like Power Query and Power Pivot.
|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.
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.
|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.
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.
|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.
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.
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...
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.
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.
The course website 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.
|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:
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). 🙂
Right now I'm offering lifetime access to The Ultimate Lookup Formulas Course for a limited time price of $197.
I love online video courses because they allow me to provide you with training at a much lower price than live in-person seminars.
A 3-day seminar can run well over $3,000, not to mention travel costs and the fact that you typically have to sit in a stuffy conference room with so many distractions, that you probably end up retaining about 5% of what was taught...
With the Ultimate Lookup Formulas Course you can watch the videos any time you want, ask me questions, and be part of a much larger community of learners.
It's a fantastic way to learn because you are able to pause the instruction and go practice what you've learned. Every task is different, and it is great to be able to practice what you have learned on your own work.
This course will allow you to learn at your own pace in an efficient and easy step-by-step manner.
There's no risk with my 30-day Money-Back Guarantee
My goal is to help you learn the lookup formulas and data techniques so you can take your career to the next level. I want you to love this course and use it as a resource for continued learning. If you take the course and are not satisfied with what you learned, you can get a full refund within 30 days.
There are no hoops to jump through. If you're not smiling, then I want to know about it. 🙂
Here's what you need to do to get started. Choose a package below and click the Enroll Now button.
All the modules and videos I discussed are there, and I will be in the comments section answering your questions. Watch the welcome video to get an overview of the course and how to use the software.
Again, I want to thank you for joining me on this journey and I look forward to seeing you on the inside... Please click the button below to get started right away.
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 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.
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.
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.
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.
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.
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.
All major credit cards including Visa, MasterCard, and American Express are accepted. You can also pay with PayPal.
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! 🙂
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.
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!
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! 🙂