Maximize Excel: Using Advanced Lookup Functions

CPE Credit: 2 hours
 
If you’ve been concerned about the integrity and resilience of your spreadsheets, you’ll find this on-demand webcast presented by Excel expert David Ringstrom, CPA, quite helpful. David delves deep into a variety of worksheet lookup functions and shares multiple troubleshooting techniques that will enable you to work more efficiently in Excel. The presentation begins with an overview of the popular VLOOKUP function, contrasting it with the HLOOKUP and VLOOKUP functions. You’ll also learn how to take lookup functions further with the Data Validation and Table features and how to implement next-level lookup functions, such as MATCH/INDEX, CHOOSE, SUMIF, and SUMIFS.
 
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.


Topics Covered

• Using VLOOKUP to perform approximate matches.
• Removing the Table feature from a worksheet if it’s no longer needed.
• Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches.
• Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
• Learning what types of user actions can trigger #REF! errors.
• Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
• Eliminating inputs that could cause VLOOKUP to return #N/A with Data Validation.
• Identifying situations where VLOOKUP may return #N/A instead of a value.
• Using the TEXT function to force lookup values to match text-based table arrays.
• Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
• Using the SUMIFS function to sum values based on multiple criteria.

Learning Objectives:

• Recall which menu contains the Text to Columns wizard.
• State what the MATCH function returns when the lookup_value is found.
• Identify how to improve the integrity of your worksheets by restricting others from entering date changes.

Course Number: 
DR1922
NASBA Field of Study: 
Computer Software and Applications
Level: 
Intermediate
Presenter:
David Ringstrom
Prerequisites: 
Experience Working with Excel Lookup Functions
Advanced Preparation: 
None