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)
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.
• 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.
• 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.
Experience Working with Excel Lookup Functions