Beyond Excel's VLOOKUP Function (Completed)

Excel expert David Ringstrom, CPA, explains helpful ways you can improve the integrity of your spreadsheets using Excel’s lookup functions. While many users rely on VLOOKUP to return data from other locations in a worksheet, that’s not always the most efficient approach. In this comprehensive presentation, David reveals alternatives to VLOOKUP, including the HLOOKUP, INDEX and MATCH, SUMIF, SUMIFS, SUMPRODUCT, IFNA, MAXIFS, and OFFSET functions.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2019. He draws your attention to any differences in Excel 2016, 2013, or 2010 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 can benefit by using a variety of lookup functions to work more efficiently in Excel.

Topics Covered

• Using the SUMIFS function to sum values based on multiple criteria
• Learning what types of user actions can trigger #REF! errors
• Future-proofing VLOOKUP by using Excel's Table feature versus referencing static ranges
• Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need
• Utilizing Excel's IFERROR function to display alternate values when VLOOKUP returns an error
• Using VLOOKUP to perform approximate matches
• Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches
• Improving the integrity of spreadsheets with Excel's VLOOKUP function
• Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces
• Identifying the risks of the LOOKUP function in Excel
• Removing the Table feature from a worksheet if it's no longer needed

Learning Objectives

• Differentiate the arguments for the INDEX worksheet function
• Describe the arguments for Excels' VLOOKUP function
• Identify the error that the CHOOSE function returns when the index number argument exceeds the number of values it has been given to display

Level
Intermediate

Instructional Method
Group: Internet-based

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

Program Prerequisites
Experience with Lookup Functions Recommended.

Advance Preparation
None