HURRICANE ALERT: The storm Ian may affect your ability to contact customer service as we are located in Tampa FL. Please be patient as we weather the storm and we will handle any issues you may have as soon as we are able.     

Self Study eBook: Computer Software & Apps

Shop Courses

Computer Software & Apps Courses

$19.00 each Add to cart

Excel Dexterity Advanced Lookup Functions

  • Credits: 2
  •  
  • Format: Self-Study eBook
  • Field of Study: Computer Software and Apps
  • Author/Speaker: David Ringstrom
Available Formats:
Advanced Preparation:None
Experience Level:Basic
Program Prerequisites:General Understanding of Lookup Formulas
Course ID:DR-610.S-22
Published Date:Mar 2022
COURSE DESCRIPTION

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:
NASBA