Excel Campus Logo Tagline White Transparent 400x56

The Ultimate Lookup Formulas Course

The Step-by-Step System for Mastering Excel's
Most Important Functions & Formulas

The Ultimate Lookup Formulas Course is currently closed for enrollment.

But not to worry, it will be open again soon. Please click the button below to get notified.

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.

Why?

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.

Lookup Functions in Excel 682x385

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!!!"

Jon Acampora VLOOKUP is 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.

Address Lookup Search for a Name and Return Matching Results

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.

Replace Complex IF Formulas with Simple Lookups

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.

Ulitmate Lookup Formulas Logo 693x389

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

Humberto S.

"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."

Ryan W. Business Analyst
Sara L. Accountant

"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."

Kevin R. Engineer

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.

Step By Step Learning Excel

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

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.

Learn How VLOOKUP Works at Starbucks

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

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.

Coffee Price Calculator GIF

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

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 Instead of 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

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.

Handle Formula Errors Like a Super Hero

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... ๐Ÿ™‚

Heather R. Customer Support Manager

"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

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.

Save Time with Excel Tables

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

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.

Create Lookup Tables in Excel
  • 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

In this module we learn more advanced techniques for VLOOKUP and INDEX MATCH.

Advanced Lookup Formula Techniques

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

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.

Download Excel Course Files - Ultimate Lookup Formulas

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.

Quick Guide to INDEX MATCH Instead of VLOOKUP

Print them out and hang them on your wall...

Bonus #2: Quizzes & Homework


quiz-time 500x143

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


Comments Section Every 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

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.

VLOOKUP vs PowerPivot in Excel

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.

Your Instructor

Jon Acampora

Jon Acampora

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?

How long do I have access to the course?

How long does it take to complete the course?

Can I download the Excel files used in the course?

Can I watch the videos on my iPad or tablet?

What version of Excel do I need?

How do I ask questions and get support?

What payment methods do you accept?

Can my employer pay for my enrollment?

Can I purchase multiple memberships for my employees?

What if I am unhappy with the course?

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!

Bob A.
Network Deployment

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!)

Victor P. 
Accountant

This [module 1] is an awesome tutorial module Jon! I feel much more confident with the VLOOKUP function now! ๐Ÿ™‚

Lucy H.
Financial Analyst

The Ultimate Lookup Formulas Course is currently closed for enrollment.

But not to worry, it will be open again soon. Please click the button below to get notified.