Beyond Excel's VLOOKUP Function (Completed)

Date: Thursday, July 9, 2020
Instructor: David H. Ringstrom
Begin Time:  9:00am Pacific Time
10:00am Mountain Time
11:00am Central Time
12:00pm Eastern Time
CPE Credit:  2 hours for CPAs

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 the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) 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.

Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Who Should Attend
Practitioners who can benefit by using a variety of lookup functions to work more efficiently in Excel.

Topics Covered

  • Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces
  • Utilizing Excel's IFERROR function to display alternate values when VLOOKUP returns an error
  • Discovering how to use wildcards and multiple criteria within lookup formulas
  • Identifying situations where VLOOKUP may return #N/A instead of a value
  • Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts
  • Eliminating inputs that could cause VLOOKUP to return #N/A with Data Validation
  • Learning what types of user actions can trigger #REF! errors
  • Transforming numbers stored as text into values by way of the Text to Columns wizard
  • Using the SUMIFS function to sum values based on multiple criteria
  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP
  • Using the SUMIFS function to sum values based on multiple criteria
  • Future-proofing VLOOKUP by using Excel's Table feature versus referencing static ranges

Learning Objectives

  • Identifyand apply a variety of lookup functions in order to work more efficiently in Excel
  • Recognize why the INDEX and MATCH combination is often superior to VLOOKUP or HLOOKUP
  • Identify 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

 Chat — Books Support