Self-Study
5
Intermediate
Computer Software and Applications
Familiarity with Excel and how to use basic functions such as SUM and COUNT. Assumes the user understands what an absolute cell reference is.

Course Description

Knowing how to use lookup and reference functions is critical to optimizing Excel. With easy-to-follow exercises and examples, this course provides a comprehensive look at Excel's Lookup and Reference category. You'll learn how to use VLOOKUP (1) with Wildcards, (2) to search through multiple sheets for a match, and (3) when a match is not in the first column of your table. This course also covers other functions that can be combined with VLOOKUP, advantages and disadvantages of using CHOOSE, how the OFFSET function allows you to set up calculations that automatically update as data is added, and how the INDIRECT function can help you find data on other sheets in your file. Become an Expert on Excel Lookup Functions is a must for financial professionals seeking to update their arsenal of Excel skills.



Instructor

Patricia McCarthy, MBA

Patricia McCarthy, MBA, is a partner at CFO Resources LLC, a company focused on training and courseware for CPAs. Her focus is on Microsoft products, particularly Excel, and how to use these programs effectively in business. She’s also certified as an MOS expert in Excel. Patricia has been in software training for over 20 years, and her client list includes the Indiana CPA Society, which awarded her its Outstanding Discussion Leader award.

She has been a contributor to the Journal of Accountancy and has published articles in several CPA society magazines, including societies in Indiana, Missouri, Texas, and Arizona.

Patricia is a graduate of the College of the Holy Cross and also holds an MBA from Babson College. She was a full-time lecturer in Butler University’s School of Business for 6 years and has taught at several of Indiana’s other universities.

Patricia also has an extensive business background in audit and financial functions. She runs a blog called Chatting about Excel and More, and she writes self-study CPE courses on Microsoft Excel for Western CPE.



Course Specifics

Computer Software and Applications
Mar 16, 2017
Familiarity with Excel and how to use basic functions such as SUM and COUNT. Assumes the user understands what an absolute cell reference is.
SS2164583
101
Excel 2007/2010/2013/2016


Compliance Information

103220
Qualifies for CA Fraud: No


Learning Objectives

Upon successful completion of this course, participants will be able to:

  • Recognize the syntax, location, characteristics, and use of the functions LOOKUP, VLOOKUP, CHOOSE, and HLOOKUP in Excel
  • Recognize the usage and location of the data validation feature
  • Recognize the characteristics, treatment, and creation of range names in Excel
  • Recognize the syntax and usage of wildcards and other functions such as COLUMN, ROW, IFERROR, ISERROR in a VLOOKUP function
  • Recognize the syntax and characteristics of an INDEX MATCH function
  • Recognize the syntax and use of OFFSET to create dynamically updated data
  • Use the INDIRECT function to retrieve information
  • Recognize the syntax and application of other Lookup and Reference functions such as COUNT and HYPERLINK


Table of Contents

Learning Objectives

Overview of LOOKUP Functions

LOOKUP

Data Validation

Review Questions

VLOOKUP

Create a Range Name

Review Exercise 1

Use VLOOKUP to find Price

Review Questions

VLOOKUP with IFERROR

Review Exercise 2

Use IFERROR and VLOOKUP to find Quantity

VLOOKUP and Wildcards

Review Exercise 3

Combine VLOOKUP and Wildcard

Review Exercise 4

VLOOKUP and Wildcard

Review Questions

Combining VLOOKUPS

Review Exercise 5

Use two VLOOKUPs to pull quantity and cost data and then multiply to determine inventory cost.

Examples for VLOOKUP with Other Functions

VLOOKUP and IF

VLOOKUP across Multiple Sheets

Review Exercise 6

Create a VLOOKUP across multiple sheets.

Review Questions

USING VLOOKUP FOR MULTIPLE RESULTS

VLOOKUP and ROW() and COLUMN()

VLOOKUP and COLUMN()

VLOOKUP and ROW()

Review Exercise 7

Drawback to VLOOKUP

CHOOSE

CHOOSE vs. VLOOKUP

VLOOKUP Searching in a Different Column for a Match

Review Exercise 8

Use VLOOKUP and CHOOSE to find the customer’s salesperson.

CHOOSE and SUM

HLOOKUP

Review Exercise 9

Use HLOOKUP to find March’s Account 100 Total at Row 15.

Review Questions

INDEX and MATCH

Review Exercise 10

Use INDEX MATCH to find the discount based upon quantity purchased.

Review Questions

OFFSET

Review Exercise 11

Create a dynamic function that displays the last 30 days of sales.

INDIRECT Function

HYPERLINK

Review Question Answers and Rationales

Glossary

Index

Final Examination

Answer Sheet

Course Evaluation

 



Choose Your Preferred Format


Online Access

Get immediate access to a robust collection of learning and reference materials, allowing you to dive deep into the information you need. Our self-study materials are authored by top-quality, industry experts who focus on helping you grasp concepts quickly using real-life examples. Download your CPE to any device, and take it with you so you can learn when and wherever you want. Complete your CPE with an online exam, and enjoy instant grading with the option to print your certificate immediately upon passing.

online access
Hard Copy

Our self-study materials are authored by top-quality, industry experts. You’ll receive a bound notebook of all the course materials, shipped to you within one business day. In addition, you’ll also have full online access. Each self-study package includes a robust collection of learning and reference materials to help you cover the information efficiently and put it into practice immediately.

hard copy
Self-Study Video

Experience high-quality instruction with our Self-Study Videos, available to you 24/7. With more than 80+ videos currently in the Western CPE Self-Study Video library, you can see and hear our expert instructors deliver the information you need in a dynamic way that allows you to immediately apply your learning. Unlike a live course or webcast, you can complete the course on your own time, playing and pausing as needed. Self-Study Videos allow you to complete your CPE requirements on your own time.

hard copy


Back to top