Learning Intermediate Concepts of Functions Using Text, IF, and VLOOKUP Functions
|Duration:||3½ - 4 hours|
|Prerequisite:||This class is a continuation of the Essentials of Formulas and Functions class. You should take that class before this one unless you already have the equivalent experience and understand function syntax and how to build simple arguments.|
|Course Materials:||Text Book with exercise file download|
|Prices:||$175 for 1 student (one-on-one training)
$125 each for 2 to 3 students
$110 each for 4 to 6 students
$110 for the first 6 students, $90 for each additional student
This class is focused on nesting functions. A nested function is a function that is used as an argument or part of an argument. Several functions can be nested in one main function. Nesting functions is what enables you to write powerful formulas. We will also create some powerful formulas by concatenating functions and text strings. If these concepts sound a little scary, don't worry—we will learn these concepts step-by-step.
To learn these concepts, we will use some of the most useful functions in Excel. We begin by learning some very basic text functions to enable us to manipulate text.
For those who need to use Excel effectively, the two most useful functions that you can learn is the IF and VLOOKUP functions. Knowing and using these two functions will elevate you to a new level as an Excel user. This class is designed to thoroughly teach you the IF function and the VLOOKUP function.
We learn the basic syntax of the IF function and create basic IF statements. We then discuss the many ways to use the IF function including learning to use other functions as arguments for the IF function. We also learn how to create IF statements with multiple conditions.
We then go through the same process to learn the VLOOKUP funcition, starting with the basics and graduating to more complex statements. Ultimately, we nest the VLOOKUP in an IF function to create a formula that saves a great deal of time entering data.
Here is the outline (if you want to suggest alternate topics, you can do so):
Basic Text Functions
- Joining Text Strings
- Extracting Portions of Text
- Counting Characters in a String
- Removing Unwanted Spaces
- Nesting Functions
- Using Functions to Do Math
The Anatomy of the IF Function
- IF Function Description
- Nesting an IF Function
- Multiple Nested Conditions and Multiple Return Values
- Nesting Other Functions in an IF Function
- Information Functions and Other Logical Tests
The Anatomy of the VLOOKUP Function
- The Function Description
- Building a Basic VLOOKUP Function
- Using the VLOOKUP Function to Fill in Data
- Using the VLOOKUP Function to Find an Approximate Match
- Nesting a VLOOKUP in an IF Function
- Concatenating Formulas and Text Strings to Build Multiple VLookup Returns