LookUp Functions - Example by Example - Microsoft® Office Excel®
Course Content
Module 1: Introducing LOOKUP functions
In this module students are shown a variety of lookup function and examples where lookup functions are needed. The basic knowledge given here is then extended throughout the course. The whole spectrum of functions that are explained in detail in future Modules are introduced here.
Lessons
Example by using the IF function
Which LOOKUP functions are at our disposal
Other functions we will use during this course
What knowledge should we possess before moving on
Module 2: The LOOKUP Functions Basics
In the course of this module, all lookup functions will be introduced step by step. With the knowledge of the syntax, we will examine examples of use and then test our knowledge in Lab 1.
Lessons
LOOKUP function
VLOOKUP function
HLOOKUP function
Lab : LOOKUP function
Practice 1: the use of LOOKUP function and both its syntaxes.
Practice 2: practice with many tricks of use and providing a deeper understanding LOOKUP function.
Lab : VLOOKUP function
Practice 3: the use of VLOOKUP functions final argument.
Practice 4: Tricks of using TRUE as the final argument and IFERROR function to prevent the #N/A error.
Lab : HLOOKUP function
Practice 5: the use of HLOOKUP function and comparison to the VLOOKUP function.
Practice 6: The use of IF and LEN functions to avoid a “zero” value when the cell we lookup is empty.
Module 3: Advanced LOOKUP examples with function nesting
This module will be instructor led example by example upgrading of lookup functions with other functions and data validation. Each function will be followed by practices. Also array functions and their use will be mentioned.
Lessons
MATCH function
How can we use MATCH with VLOOKUP
INDEX function
Combining MATCH and INDEX functions
INDIRECT function
CHOOSE function
CELL function
OFFSET function
ROW and COLUMN functions