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.
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
Overview of LOOKUP Functions
Create a Range Name
Review Exercise 1
Use VLOOKUP to find Price
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 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.
USING VLOOKUP FOR MULTIPLE RESULTS
VLOOKUP and ROW() and COLUMN()
VLOOKUP and COLUMN()
VLOOKUP and ROW()
Review Exercise 7
Drawback to VLOOKUP
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
Review Exercise 9
Use HLOOKUP to find March’s Account 100 Total at Row 15.
INDEX and MATCH
Review Exercise 10
Use INDEX MATCH to find the discount based upon quantity purchased.
Review Exercise 11
Create a dynamic function that displays the last 30 days of sales.
Review Question Answers and Rationales
Choose Your Preferred Format
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.
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.
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.
Back to top