Beyond Excel's VLOOKUP Function (Currently Unavailable)

Author: David H. Ringstrom

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 course.

Publication Date: July 2019

Designed For
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
• Describe uses of Data Validation
• Recognize and apply the IFERROR function
• Identify whichdata integrity improvement does using the Table feature in conjunction with VLOOKUP provides
• Recognize what the LOOKUP function will return if an exact match or a match on the look-up cannot be made or found
• Describe the ISNUMBER function
• Recognize which ribbon tab or menu in Excel the Text to Columns command appears on
• Identify what MATCH returns when the lookup_value is found
• Describe the arguments the INDEX function has

Level
Intermediate

Instructional Method
Self-Study

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

Program Prerequisites
Experience with Lookup Functions Recommended.

Advance Preparation
None