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!

Sometimes you'll find yourself working with dates in an Excel spreadsheet that have been pasted or imported into Excel from another datasource. When that happens, Excel can treat those dates as text - in other words, they look like dates but don't behave like dates. For example you can't sort by date properly. This lesson looks at several ways you can convert a date which Excel is treating as text into a proper date value in Excel.

Sometimes you'll find yourself working with dates in an Excel spreadsheet that have been pasted or imported into Excel from another datasource. When that happens, Excel can treat those dates as text - in other words, they look like dates but don't behave like dates. For example you can't sort by date properly. This lesson looks at several ways you can convert a date which Excel is treating as text into a proper date value in Excel.

This lesson shows you now to extract text from a cell in Excel. This is useful when you have a cell containing combining numbers and text, such as a part number, or several text values separated by commas. It introduces the RIGHT() and LEFT() functions, which are essential text manipulation functions in Excel.

This lesson shows you now to extract text from a cell in Excel. This is useful when you have a cell containing combining numbers and text, such as a part number, or several text values separated by commas. It introduces the RIGHT() and LEFT() functions, which are essential text manipulation functions in Excel.

This lesson shows you a way to calculate the number of times a single character occurs in a cell in Excel, and provides a real-life example where I needed to split a column of cells containing part numbers into individual components for each part number.

There are many scenarios where you may need to use the current date and time in your spreadsheets. You may simply need to display the current date in a spreadsheet report. Or, you may need to perform a calculation that uses the current date or time. This lesson shows you how to enter a formula into a cell in Excel that outputs the current date and/or time, and updates automatically as time passes.

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.

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.

## Pages |