Excel 101: Lookup Formulas (Completed)

In this session, Excel expert David Ringstrom, CPA, introduces you to several lookup functions, including VLOOKUP, HLOOKUP, MATCH, and CHOOSE. These powerful functions allow you to rapidly develop accurate spreadsheets and find information, such as pay rates, item prices, accounting results, instead of manually linking to specific cells. David explains the context of when to use lookup functions, demonstrates troubleshooting techniques, and prepares you to deal with subtle issues that can prevent them from working properly.

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.

Who Should Attend
Practitioners who would like to work more efficiently in Excel by using lookup functions, such as VLOOKUP.

Topics Covered

• Learning about the MAXIFS function available in certain versions of Excel 2016
• Improving the integrity of spreadsheets with Excel's VLOOKUP function
• Using VLOOKUP to perform approximate matches
• Seeing what types of user actions can trigger #REF! errors
• Using the IFERROR function to display something other than an #N/A error value when VLOOKUP can't find a match
• Learning about the MINIFS function available in certain versions of Excel 2016
• Saving time when aggregating data from multiple worksheets with Excel's INDIRECT function
• Seeing how to view two worksheets from within the same workbook at the same time
• Using Excel's OFFSET function to dynamically reference data from one or more accounting periods
• Making VLOOKUP look up data from the left by using the CHOOSE function
• Using the MATCH function to find the position of an item in a list
• Using Excel's TRIM function to eliminate extraneous spaces that can compromise lookup functions

Learning Objectives

• Identify and apply the VLOOKUP and HLOOKUP functions
• Define how to improve spreadsheet integrity with Excel's VLOOKUP function
• Differentiate how to use the MATCH worksheet function to identify differences between two lists

Level
Basic

Instructional Method
Group: Internet-based

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
None

Advance Preparation
None