# Table of Contents: Advanced Excel Functions

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

#### Introduction

#### Introduction

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

#### Prerequisites

#### Prerequisites

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

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?

#### HLOOKUP

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

#### INDEX MATCH

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

#### INDIRECT

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.

#### OFFSET

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?

#### SUMIF

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

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?

#### COUNTIF

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

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

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

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

#### AVERAGEIFS exercise

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.

#### Conclusion

#### Conclusion

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