Pragmatic Excel - VLOOKUP and Alternatives

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.

Level:

Intermediate

Format:

On-Demand Webcast

Instructional Method:

Self-Study

Program Prerequisites:

Experience Working with Excel Lookup Functions

Advance Preparation:

None