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 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 can benefit by using a variety of lookup functions to work more efficiently in Excel.

Topics Covered

• Restrict users to enter dates within a given range or before/after a given date
• Learn about the MINIFS function available in certain versions of Excel 2016
• Use the SUMIF function to summarize data based on a single criterion
• Discover the capabilities of the SUMPRODUCT function
• Save time when aggregating data from multiple worksheets with Excel's INDIRECT function
• Learn how VLOOKUP stops looking after it finds an initial match within a list
• Learn why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP
• See how the HLOOKUP function enables you to perform horizontal matches
• Use Excel's OFFSET function to dynamically reference data from one or more accounting periods
• Learn why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP
• Learn about the IFNA function available in Excel 2013 and later
• See how to view two worksheets from within the same workbook at the same time
• Learn about the MAXIFS function available in certain versions of Excel 2016

Learning Objectives

• Identify and apply a variety of lookup formulas in order to work more efficiently in Excel
• Recognize why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP
• Differentiate and apply the SUMIF and SUMIFS functions

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