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 SHOULD YOU 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.
AREA 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
LEARNING OBJECTIVES
- 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
WHO WILL BENEFIT?
- CPAs and Accountants
- Marketing and Management Consultants
- Bankers
- CFOs
- Controllers
- Financial Planners and Consultants
- Business Professionals
- Business Analysts
- Bookkeepers
- Excel Users
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.
- 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
- 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
- CPAs and Accountants
- Marketing and Management Consultants
- Bankers
- CFOs
- Controllers
- Financial Planners and Consultants
- Business Professionals
- Business Analysts
- Bookkeepers
- Excel Users