Excel Proficiency: Advanced Lookup Functions

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.

• Apply a range of lookup functions to work more efficiently in Excel
• Explain why INDEX and MATCH often outperforms VLOOKUP or HLOOKUP
• Apply the SUMIF, SUMIFS and SUMPRODUCT functions
• Perform horizontal matches with HLOOKUP
• Use OFFSET and INDIRECT to reference data dynamically
• Protect worksheet integrity by restricting date entries
• View two sheets from the same workbook side by side

• 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

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.

• CPAs and Accountants
• Marketing and Management Consultants
• Bankers
• CFOs
• Controllers
• Financial Planners and Consultants
• Business Professionals
• Business Analysts
• Bookkeepers
• Excel Users

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.