Table of Contents: Advanced Excel Functions

Lost? No problem! Here's a complete table of contents for this course:



Get started here - we'll go over what you'll learn in this course and how to navigate through sections and lessons.



Before diving into our course, there are a couple of important prerequisite lessons we'll need to learn. Don't worry - they're minimal!

What is a function?

What is a function in Excel? Our helpful tutorial will walk you through the basics of what a function is and how to use one in your spreadsheet.

Cell references

Excel has two types of cell references: Relative and absolute. Relative references shift as you copy and paste cells. Absolute references do not.

Paste special

The Paste Special command has many uses, but we'll use it to convert formulas to values before we create our charts.

Basic lookups


VLOOKUP, one of Excel's most powerful functions, is used to look up data from a foreign table. Learn how to do a VLOOKUP in this handy tutorial!

VLOOKUP exercise

Can you create a basic VLOOKUP function to pull data from an external table?


Ever wondered whether it's possible to do a VLOOKUP horizontally rather than vertically? It's easy with the HLOOKUP function!

HLOOKUP exercise

Use your newly-learned HLOOKUP function to pull data from a table that's oriented horizontally rather than vertically.



INDEX MATCH, one of Excel's most useful functions, is an improved alternative to VLOOKUP.

INDEX MATCH exercise

Let's try using INDEX MATCH in a real-world scenario and explore why it's a better alternative to VLOOKUP for many applications.

MATCH types

What exactly does that 'match_type' argument mean? Here, we'll explore the different MATCH types available in Excel and explain when each of them is most often used.

MATCH exercise

Experiment with different MATCH types in an INDEX MATCH formula to solve a distinct set of analytical problems.

Advanced lookups

Two-dimensional lookups

The INDEX function isn't just one-dimensional: it can accept a 'row' as well as a 'column' argument for more complex lookups in two dimensions.

Two-way lookup exercise

Try your hand at using the INDEX MATCH MATCH formulation to dynamically pull values from a two-dimensional data set.

Multi-criteria lookups

INDEX MATCH MATCH works well for data sets that are well laid-out - but what if our data is all in one column? Good news: we can still perform lookups based off of multiple criteria. But things get a bit more difficult!

Expert lookup exercise

Let's bring everything we've learned together by using array formulas to complete an ultra-complex lookup based on multiple criteria.

Lookup helpers


The INDIRECT function is a helpful add-on to INDEX MATCH and VLOOKUP's functionality. It enables us to choose lookup tabs in real time.

INDIRECT exercise

Use the INDIRECT function to select data from multiple tabs - at the same time.


The OFFSET function moves a selected data range by a specified number of cells vertically or horizontally.

OFFSET exercise

Can you use the OFFSET function to select a lookup table in real-time based on a dynamic criterion?

Conditional functions

The IF function

The IF function serves as the backbone of many of Excel's more advanced conditional formulas. Let's learn how to use it before moving on to our expert-level functions.

IF exercise

Can you use your knowledge of the IF function to construct conditional formulas that evaluate dynamic criteria within a workbook?


Excel's SUMIF function allows you to take the sum of a column or row of data conditional upon a particular criteria row.

SUMIF exercise

Let's take what we've learned about SUMIF and apply it to a real-world data table.


SUMIFS is very similar to Excel's SUMIF function, but it allows you to take the sum of a range based on multiple criteria rather than using a single condit

SUMIFS exercise

Can you modify our SUMIF formula slightly to accommodate multiple criteria within our data table?


The COUNTIF function allows you to count the number of occurrences of a specified criteria within a given cell range.

COUNTIF exercise

Let's try using conditional formulas with a COUNT rather than a SUM as the result.


COUNTIFS allows you to count the number of rows in a table that satisfy multiple criteria across as many columns as you want.

COUNTIFS exercise

Can you add multiple criteria to our COUNTIFS formula to accommodate multiple columns within our data set?


AVERAGEIF, like its cousins SUMIF and COUNTIF, allows us to take the average of a range based on a specified criteria.

AVERAGEIF exercise

Let's modify our conditional SUM and COUNT formulas to take the AVERAGE of values within our data set, isntead.


AVERAGEIFS is the multi-criteria version of the AVERAGEIF function, allowing us to AVERAGE a range based on multiple criteria.


Finally, we'll try our hand at using the AVERAGEIFS formula to take the AVERAGE of rows in a dataset, contingent upon multiple criteria being satisfied.

Culminating exercise

Culminating exercise

In this culminating exercise, we'll bring together everything we've learned in this course to solve a complex data organization and analytical challenge for our manager.



This marks the conclusion of our course! Check in here to review what we've learned.

Advanced Excel Functions: 0 of 37 lessons completed