Beyond VLOOKUP: Mastering Advanced Excel Functions

  EVENT DATE

October 25, 2017

  PRESENTER(s)

David H. Ringstrom, CPA

  1:00 PM ET | 12:00 PM CT | 10:00 AM PT | 60 Minutes




DESCRIPTION



Microsoft Excel is an invaluable and indispensable tool for every office employee, in almost every industry. One of the most important formulas in Excel are the Look-up formulas, used to see individual entries in large data sheets.

Look-up formulas are a useful tool to compare data entries and maintain accuracy in records, especially in large data sheets and are one of the best ways to improve the integrity of Excel spreadsheets. Proper use of Look-up functions can reclaim a part of your day, as you get better at finding the information you need from vast repositories of data.

Many users rely on VLOOKUP for basic look-up functionality in spreadsheets, but are often unaware of ways to improve the integrity of this venerable function. In this session Excel expert David H. Ringstrom,CPA - introduces the VLOOKUP function, and then quickly goes beyond the basics. Discover what can go awry with VLOOKUP, how to future-proof the function, and explore alternatives such as MATCH/INDEX, SUMIF, SUMIFS, SMALL/LARGE, SUMPRODUCT, IFNA,MIN/MAX, and OFFSET.

Learning Objective Points:

  • Restricting users to enter dates within a given range or before/after a given date.
  • Learning about the MINIFS function available in certain versions of Excel 2016.
  • Using the SUMIF function to summarize data based on a single criterion.
  • Discovering the capabilities of the SUMPRODUCT function.
  • Saving time when aggregating data from multiple worksheets with Excel’s INDIRECT function.
  • Learning how VLOOKUP stops looking after it finds an initial match within a list.
  • Learning how the HLOOKUP function enables you to perform horizontal matches.
  • Using Excel’s OFFSET function to dynamically reference data from one or more accounting periods.
  • Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP.
  • Learning about the IFNA function available in Excel 2013 and later.
  • Learning about the MAXIFS function available in certain versions of Excel 2016.

Session Highlight Points:

  • Apply a variety of lookup functions in order to work more efficiently in Excel.
  • Recognize why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP.
  • Apply the SUMIF and SUMIFS functions.
  • Use Excel’s OFFSET function to dynamically reference data from one or more accounting periods.
  • Learn about situations where VLOOKUP may return an error or omit desired data.
  • Improve the integrity of VLOOKUP by way of the MATCH function and/or Excel's Table feature.
  • Utilize the SUMIFS function in Excel 2007 and later for multiple-criteria look-ups.

Who should attend:

  • Accountants
  • Investment Bankers
  • CPA/CFO/Controllers
  • Financial Consultants
  • Management Consultants
  • Strategic Consultants
  • Marketing Manager
  • Business Intelligence Analysts
  • HR professionals
  • Payroll Executives







RELATED WEBINARS

 


CERTIFICATION PARTNERS

 
HRCI
HRCI
HRCI
HRCI