Excel Proficiency: Advanced Lookup Functions
Learn alternatives to VLOOKUP, including INDEX and MATCH, HLOOKUP, SUMIFS, SUMPRODUCT, OFFSET and INDIRECT, to build faster, more reliable Excel spreadsheets.
Speaker
David H. Ringstrom
CPA and Excel consultant · Accounting Advisors, Inc. · CPA
Overview
Many Excel users rely on VLOOKUP to pull data from elsewhere in a workbook, but it isn't always the most efficient choice. This 90-minute advanced Excel lookup functions training shows better ways to retrieve data and improve spreadsheet integrity.
You'll see why VLOOKUP stops at the first match, how HLOOKUP handles horizontal matches, and why INDEX and MATCH is often the better option. The session also covers SUMIF, SUMIFS and SUMPRODUCT for summarizing data, OFFSET for referencing accounting periods dynamically, INDIRECT for pulling data from multiple sheets, and IFNA, MINIFS and MAXIFS.
Each technique is demonstrated step by step, along with tips such as restricting date entries and viewing two worksheets at once.
Why attend: Knowing a range of lookup functions lets you work more efficiently and build spreadsheets that are less prone to errors. You'll see each technique explained with numbered steps and then shown in Excel. It suits CPAs and accountants, CFOs, controllers, bankers, financial planners, consultants, business analysts, bookkeepers and other Excel users.
What’s covered
- Restricting users to dates within a range or before or after a given date
- The MINIFS and MAXIFS functions
- Summarizing data with SUMIF and SUMIFS
- What the SUMPRODUCT function can do
- Aggregating data from multiple worksheets with INDIRECT
- Why VLOOKUP stops after the first match
- Horizontal matches with HLOOKUP
- Dynamically referencing accounting periods with OFFSET
- Why INDEX and MATCH often beats VLOOKUP or HLOOKUP
- The IFNA function
- Viewing two worksheets from the same workbook at once
Who should attend
What you get
About the speaker
David H. Ringstrom is a CPA and owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. David speaks at conferences and presents dozens of webinars annually on Excel and other topics. He has written numerous articles on spreadsheets, some of which have been published internationally. He has served as the technical editor for other 30 books on accounting software, and is the Tech Editor-at-Large for Accounting WEB and Going Concern. David offers Excel training and consulting services nationwide.
Staci Warne · 60 min
Cathy Horwitz · 180 min
Craig Brody · 60 min