Course 50559-A: Lookup Functions

Microsoft

Course 50559-A: Lookup Functions

Share This Learning Path

This two-day instructor-led course provides students with the knowledge and skills to write basic Excel lookup formulas as well as in depth nesting of advanced array and reference functions. The purpose of the course is not only to show that certain functions exist and how they look like, but to show the diversity and usefulness of those functions. This is a very hands-on approach with constant examples, practices and problem solving provided to the audience.

Audience Profile

This course is intended for both novice and experienced information worker who have the understanding and knowledge of basic Excel formulas and functions. A minimum one year experience with Excel is recommended before attending.

Prerequisites

Course outline

Module 1: Introducing Lookup Functions

Module Overview

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

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

What are we trying to accomplish.
Why should we use LOOKUP functions.
What LOOKUP functions are at my disposal.
Which reference functions should I know.
Which Array functions should I know.
Recognize the situations where lookup functions are used.

Module 2: The Lookup Functions Basics

Module Overview

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.

Lessons

LOOKUP function
VLOOKUP function
HLOOKUP function

Lab Sessions

LOOKUP function
VLOOKUP function
HLOOKUP function

Lab Lessons

The use of LOOKUP function and both its syntaxes
Practice with many tricks of use and providing a deeper understanding LOOKUP function
The use of VLOOKUP functions final argument
Tricks of using TRUE as the final argument and IFERROR function to prevent the #N/A error
The use of HLOOKUP function and comparison to the VLOOKUP function
The use of IF and LEN functions to avoid a zero value when the cell we lookup is empty

After completing this module, students will be able to:

Recognize a LOOKUP function syntax.
Recognize situations when we use the LOOKUP function.
List how does LOOKUP differ from other lookup functions.
Recognize a VLOOKUP function syntax.
Recognize what kinds of examples are solved using VLOOKUP function.
List all differences from other lookup functions.
Recognize a HLOOKUP function syntax.
Tell when to use HLOOKUP.

Module 3: Advanced Lookup Examples with Function Nesting

Module Overview

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

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Enhance lookup formulas with many new functions and tools.
Recognize the need for MATCH function.
Face any situation where lookup functions are required at their daily faceoff with Excel.

Book Your Seat

Find Learning Paths

Course 20703-1-B: Administering System Center Configuration Manager

Join our community of certified professionals

About Us

ICTC is the leader in technical certification courses and exams. Our labs consist of a latest tech PCs and our instructors are certified from each vendor

Learn

View all the provided certifications and there relevant courses. Book online for a certification exam.

Contact Us

International Computer Training Center