VLOOKUP allows you to look for a specified value in a column of data inside a table, and then fetch a value from another column in the same row. An example might be where you need to find the sales for a specific salesperson from within a monthly sales report. In this lesson you'll learn how to use VLOOKUP in your spreadsheets by walking you through several simple examples. The lesson will also highlight some shortcomings of VLOOKUP, plus a solution to those shortcomings.

# Learn Excel Formulas and Functions

Excel offers over 300 functions and formulas to help you get more out of your spreadsheets. This page contains a summary of the functions that are covered by lessons on this site. It's being added to all the time, so check back often!

The SUMIFS function in Excel allows you to sum the values in a range of cells that meet multiple conditions, or criteria. For example, you might use the SUMIFS function in a sales spreadsheet to to add up the **value of sales** of a **specific product** by a **given sales person** (e.g. the value of all sales of a microwave oven made by John). This lesson explains how to use SUMIFS.

The SUMIFS function in Excel allows you to sum the values in a range of cells that meet multiple conditions, or criteria. For example, you might use the SUMIFS function in a sales spreadsheet to to add up the **value of sales** of a **specific product** by a **given sales person** (e.g. the value of all sales of a microwave oven made by John). This lesson explains how to use SUMIFS.

If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use of absolute and relative references.

If you're using the Autofilter feature, you're probably wondering how to perform calculations on only those values that are being displayed by the filter. The SUBTOTAL() function is the answer.

The SUMPRODUCT function allows you to multiply two arrays of numbers together (e.g. Quantity Sold and Price Per Unit) and add the results each individual calculation together. Without the SUMPRODUCT function, you'll find yourself having to create a third column in which you multiply the Quantity by Price for each row, and then find the sum of all the individual formulas. This lesson shows you how to use SUMPRODUCT to do all that with just one formula.

The SUMPRODUCT function allows you to multiply two arrays of numbers together (e.g. Quantity Sold and Price Per Unit) and add the results each individual calculation together. Without the SUMPRODUCT function, you'll find yourself having to create a third column in which you multiply the Quantity by Price for each row, and then find the sum of all the individual formulas. This lesson shows you how to use SUMPRODUCT to do all that with just one formula.

The IF() function in Excel allows you to evaluate a situation which has two possible outcomes (e.g. sales are greater than $1000) and calculate a different value for each outcome. However, sometimes you need to work with situations where there are more than two possible outcomes. That's where multiple, or nested, IF functions come in handy. In this tutorial we'll cover how to use nested IF functions to calculate sales commission for a team of sales people, given a range of different commission rates.

If you're getting started with Excel, creating formulas is one of the first things you should learn. In this lesson you'll learn how to create simple formulas and calculations in Excel.

Excel offers a couple of handy functions that you can use to calculate the smallest and largest values in a range of cells. They are simple functions that go by the names of MIN() and MAX(). This lesson shows you how to use them. It also introduces SMALL() and LARGE(), functions which duplicate what MIN and MAX do, plus more besides.

## Pages |