If you’re concerned about the integrity and resilience of your spreadsheets, you’ll find this webcast presented by Excel expert David Ringstrom, CPA, extremely beneficial. In his intermediate-level presentation, David delves deeper into lookup functions and shares multiple troubleshooting techniques to help you work more efficiently in Excel. He provides an overview of the popular VLOOKUP function and contrasts it with the HLOOKUP and VLOOKUP functions. You’ll also learn how to take advantage of the Data Validation and Table features and how to implement next-level lookup functions, such as MATCH/INDEX, CHOOSE, SUMIF, and SUMIFS.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) 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.
Practitioners who would benefit from learning more about Excel’s lookup functions to improve the integrity of their spreadsheets and boost their efficiently.
- 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.
- Recall how to execute horizontal matches with the HLOOKUP function.
Topics Typically Covered:
- Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
- Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
- Using the MATCH function to find the position of an item in a list.
- Transforming numbers stored as text into values by way of the Text to Columns wizard.
- Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
- Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches.
- Future-proofing VLOOKUP by referencing entire columns when available.
- Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
- Identifying the risks of the LOOKUP function in Excel.
- Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
- Eliminating inputs that could cause VLOOKUP to return #N/A with Data Validation.