Transcending Excel's VLOOKUP Function

Duration: 120 Minutes
Excel expert David Ringstrom, CPA, shares some helpful tweaks you can use with the venerable VLOOKUP function in this informative presentation. He discusses alternatives to VLOOKUP, including INDEX and MATCH, SUMIF, SUMIFS, SUMPRODUCT, IFNA, and OFFSET functions.

Lookup formulas are far superior to manually pointing to specific data elements in a spreadsheet. Although many users rely on VLOOKUP to return data from other locations in a worksheet, that's not always the most efficient approach. David shows you how to perform wildcard lookups, where only partial criteria are provided. He also teaches you how to master multiple-criteria lookups.
Excel’s VLOOKUP Function
Instructor: David H. Ringstrom
Product ID: 501496
Objectives of the Presentation
  • Identify the limitations of VLOOKUP and learn about alternative functions
  • List which versions of Excel support the IFNA worksheet function
  • State why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP
  • Understand how to perform dual lookups
  • Learn what types of user actions can trigger #REF! errors
  • Understand how to improve the integrity of spreadsheets with Excel's VLOOKUP function
  • Apply the SUMIF function to summarize data based on a single criterion and the SUMIFS function to sum values based on multiple criteria
Why Should you Attend
Practitioners who wish to benefit from working more efficiently in Excel by utilizing lookup formulas must attend this webinar.

David teaches from the version of Excel in use by the majority of attendees, but he explains any differences in Excel 2007, 2010, 2013, and 2016. His webcasts are fast-paced, and attendees often are surprised at the amount of ground he covers in a session. He welcomes participants' questions, so come ready to pick his brain. David's detailed handouts serve as reference tools you can fall back on after participating in one of his webcasts. He also provides an Excel workbook that includes a majority of the examples he uses during each session.

Areas Covered
  • Avoiding the complexity of nested IF statements with Excel's CHOOSE function
  • Comparing the MIN, SMALL, MAX, and LARGE functions
  • Future-proofing VLOOKUP by using Excel's Table feature versus referencing static ranges
  • Improving the integrity of spreadsheets with Excel's VLOOKUP function
  • Learning about the IFNA function available in Excel 2013 and later
  • Learning the risks of linked workbooks, determining if a workbook contains links, and seeing how links can hide within Excel features
  • Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP
  • Making VLOOKUP look up data from the left by using the CHOOSE function
  • Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need
  • Seeing what types of user actions can trigger #REF! errors
  • Using Excel's OFFSET function to dynamically reference data from one or more accounting periods
  • Using the IFERROR function to display something other than #N/A when VLOOKUP can't find a match
  • Using the MATCH function to find the position of an item on a list
  • Using the SUMIF function to summarize data based on a single criterion and the SUMIFS function to sum values based on multiple criteria
  • Using VLOOKUP to perform approximate matches
Who can Benefit
  • Accountants and CPAs
  • CFOs
  • Financial consultants
  • Controllers
  • Accounting Personnel
  • Human Resources Personnel
Topic Background
One of the most common spreadsheet tasks is the process of looking up specific values within a data set. To do this in Excel, you can use a variety of lookup formulas, which have specific syntax rules and that reference data ranges. These formulas provide you optimal lookup functionality while preventing you from making errors in your spreadsheets.
$300
Recorded Session for one participant
Get life time access with download option!
Book this course
Pay Now
  $450.00 Training CD
Free shipment within 4 Working Days of placing the order. Get life time access for unlimited participants.
  $550.00 Training USB Flash Drive
Free shipment within 4 Working Days of placing the order. Get life time access for unlimited participants.
For multiple location please contact our customer care team +1-510-857-5896.
How it works
Live Session - How it works
  • Login to onlinecompliancepanel with your registered username and password https://www.onlinecompliancepanel.com/login
  • The webinar joining link, username and password for joining the webinar will be updated on your OCP Account 24 hours prior to the webinar
  • Presentation handouts in Downloadable PDF format will be updated on your OCP Account 24 hours prior to the live session
  • Login to the audio conference on the scheduled date and time
  • Get answers to your queries through interactive Q&A sessions via chat at the end of the session
  • Download the Certificate of Attendance and Purchase Invoice from your OCP Account 24 hours after the completion of the session
  • Please let us know your thoughts and views at the end of the webinar, your valuable feedback will help us improve
Recorded Session - How it works
  • Login to onlinecompliancepanel with your registered username and password https://www.onlinecompliancepanel.com/login
  • Upon purchase of the recorded session a link will be updated on your OCP Account within 24 hours
  • Please click on the link to access the Recorded Session
  • Presentation handouts in downloadable PDF format will be updated on your OCP Account within 24 hours of the purchase of the product
  • Download the Certificate of Attendance and Purchase Invoice from your OCP Account after 48 hours of the product Purchase
  • Please share your valuable Feedback at the end of the session
Instructor Profile:
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.
View More