Excel Competency: Intermediate Lookup Functions
EVENT DATE:
PRESENTER(s): David H. Ringstrom
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 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.
Learning objectives:
- Identify the arguments used by the SUMIF function.
- State how to perform dual lookups.
- Define the areas within a spreadsheet that the MATCH function can look.
- Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
- Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
- Using the MATCH function to find the position of an item in a list.
- Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need.
- Identifying situations where VLOOKUP may return #N/A instead of a value.
- Removing the Table feature from a worksheet if it’s no longer needed.
- Using VLOOKUP to perform approximate matches.
- Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches.
- Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
- Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
- Utilizing Excel’s IFERROR function to display alternate values when VLOOKUP returns an error.
