Are your spreadsheets lacking the integrity and resilience you wished for? This webinar presented by Excel expert David Ringstrom, CPA, will prove to be extremely insightful and helpful. We will take a deep dive into a variety of worksheet lookup functions. To empower you to work more efficiently in Excel, David will share multiple troubleshooting techniques that will boost your performance. Overview of the popular VLOOKUP function will be covered along with contrasting it with the HLOOKUP and VLOOKUP functions. Learn how to take lookup functions a few steps further with the Data Validation and Table features. We will show you how to implement next-level lookup functions, such as MATCH/INDEX, CHOOSE, SUMIF, and SUMIFS. The best thing about this online session is that David demonstrates every technique at least twice. First, on a PowerPoint slide showcasing each step, and second, in the subscription-based Office 365 version of Excel. He will also bring your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation. Detailed handouts and an Excel workbook that includes most of the examples he uses during the webcast will also be shared with the attendees. Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.
Learning objectives:
- State what the SUMIFS function returns if you link to data in another workbook that isn’t open at the time
- Identify the arguments used by the SUMIF function
- Recall what types of user actions can trigger #REF! errors
Seminar Agenda:
- Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need.
- Using the HLOOKUP function to look horizontally across rows in any version of Excel.
- Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
- 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.
- Utilizing Excel’s IFERROR function to display alternate values when VLOOKUP returns an error.
- Learning what types of user actions can trigger #REF! errors.
- Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
- Using the SUMIFS function to sum values based on multiple criteria.
- Explaining the new XLOOKUP worksheet function being rolled out to Office 365 users.
- Removing the Table feature from a worksheet if it’s no longer needed.
- Using the MATCH function to find the position of an item in a list
Who Should Attend:
- CFOs
- CPAs
- Controllers
- Investment Analysts
- Financial Analysts
- Business Analysts
- Forecasting & Planning Teams
- M&A Specialists (Mergers & Acquisitions)
- Risk Managers
- Strategic Planners
- Capital Expenditure Planners
- Cost Accountants
- Managerial Accountants
- Other Accounting and Finance Professionals
- Human Resources Departments
- IT Departments
- Auditors
- Banking Professionals
- Insurance Professionals
- Excel Users in Regulated Lifesciences Industry
- Logistics and Supply Chain Professionals
- Sales and Marketing Professionals
IT3587