Pragmatic Excel - VLOOKUP and Alternatives

Are you concerned about the integrity and resilience of your spreadsheets? If so, you’ll find this on-demand course extremely helpful. In part 2 of Lookup Functions, Excel expert David Ringstrom, CPA, delves deeper into lookup functions and shares multiple troubleshooting techniques that will...

On demand, 2 hour(s)

$99 ($0)


Register Now

Course Description

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

Who Would Be Interested in This Course:

Practitioners who would benefit from learning more about Excel’s lookup functions in order to work more efficiently.


Topics Covered:

 •      Using VLOOKUP to perform approximate matches.

 •      Removing the Table feature from a worksheet if it’s no longer needed.

 •      Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches.

 •      Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.

 •      Learning what types of user actions can trigger #REF! errors.

 •      Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.

 •      Eliminating inputs that could cause VLOOKUP to return #N/A with Data Validation.

 •      Identifying situations where VLOOKUP may return #N/A instead of a value.

 •      Using the TEXT function to force lookup values to match text-based table arrays.

 •      Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.

 •      Using the SUMIFS function to sum values based on multiple criteria.

 Learning Objectives:

•      Identify how to improve the integrity of your worksheets by restricting others from entering date changes.

•      State what the MATCH function returns when the lookup_value is found.

•      Learn how to execute horizontal matches with the HLOOKUP function.






On-Demand Webcast


Instructional Method:



Program Prerequisites:

Experience Working with Excel Lookup Functions


Advance Preparation:


Course Walkthrough

  • Course: Pragmatic Excel - VLOOKUP and Alternatives
  • CPD Quiz: Pragmatic Excel - VLOOKUP and Alternatives
  • Pragmatic Excel - VLOOKUP and Alternatives - Slides
  • Pragmatic Excel - VLOOOKUP and Alternatives - Sample File
  • Pragmatic Excel - VLOOKUP and Alternatives - Q&A
  • Feedback / comments
  • Certificate: Pragmatic Excel - VLOOKUP and Alternatives

About the Instructor(s)

Picture of David Ringstrom
David Ringstrom