Excel Dexterity: Advanced Lookup Functions (eBook)

  • Credits: 2
  • Format: Self-Study eBook
  • Field of Study: Computer Software and Apps
  • Author/Speaker: David Ringstrom
Course ID: Advanced Preparation: Experience Level:
DR-610.21 None Overview
Published Date: Program Prerequisites: Other Course Formats:
© July 2020 General Understanding of Lookup Formulas N/A

In this hands-on presentation Excel expert David Ringstrom, CPA, explains helpful ways you can improve the integrity of your spreadsheets using Excel’s lookup functions. The presentation starts out by comparing exact and approximate matches with VLOOKUP, HLOOKUP, and LOOKUP. You'll then see a number of ways to troubleshoot common problems that plague VLOOKUP and other look-up functions. The presentation closes with a discussion of other alternatives such as MATCH/INDEX, SUMIF, and SUMIFS.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel. 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.

Topics include:
  • Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
  • Using VLOOKUP to perform approximate matches.
  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
  • Future-proofing VLOOKUP by referencing entire columns when available.
  • Learning what types of user actions can trigger #REF! errors.
  • Using the SUMIFS function to sum values based on multiple criteria.
  • Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches.
  • Utilizing Excel’s IFERROR function to display alternate values when VLOOKUP returns an error.
  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
  • Using the MATCH function to find the position of an item in a list.
  • Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
  • Performing dual lookups, which allows you to look across columns and down rows to cross-reference the data you need.

Learning Objectives:

After reading the course material, you will be able to:

  • Identify what you can use in place of the word TRUE in VLOOKUP to return an approximate match.
  • Recall which menu contains the Text to Columns wizard.
  • Recall the maximum number of criteria pairs that the SUMIFS function permits.

Who Should Attend:
  • All Certified Public Accountants (CPAs)

Qualifies and Approved with all State Boards of Accountancy and the following sponsorship’s: