Leveraging Lookup Formulas – Video

Price: $29.99

CPE Credits: 2.0

Category: , ,

Course Number: VCXLOODR

lookup formulas

Description:
Excel expert David Ringstrom, CPA, introduces several lookup functions, including VLOOKUP, HLOOKUP, MATCH, and CHOOSE, in this valuable presentation. You’ll discover how to use these powerful functions to rapidly develop accurate spreadsheets and look up information, such as pay rates, item prices, and accounting results, versus manually linking to specific cells. David explains the context of when to use lookup functions, demonstrates troubleshooting techniques, and prepares you to deal with subtle issues that can prevent them from working properly. David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

  • Using VLOOKUP to perform approximate matches.
  • Seeing how the HLOOKUP function enables you to perform horizontal matches.
  • Using VLOOKUP to look up data from another workbook.
  • Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
  • Using the MATCH function to find the position of an item in a list.
  • Simplifying multiple-field lookups with concatenation (combining fields together into a single cell).
  • Learning about the MAXIFS function available in certain versions of Excel 2016.
  • Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
  • Troubleshooting other errors VLOOKUP can present such as #REF!, #NAME!, and #VALUE!.
  • Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP.
  • Using the SUMIFS function to sum values based on multiple criteria.
  • Discovering the capabilities of the SUMPRODUCT function.

Delivery Method: Online QAS Self Study.

Level: Basic to Intermediate

Version: David teaches from Excel 2010 in this presentation but every aspect of the material applies equally to Excel 2007, 2013, and 2016.

Prerequisites: A familiarity with Excel spreadsheets

Advanced Preparation: None

AuthorDavid Ringstrom,CPA

Publication: December 2012
Update: April 2018

Format:  On-demand webcast. Powerpoint handout covering video is included as well.
Run-Time: 100 minutes

Passing Grade: 70%

Exam Policies: Exam may be retaken. Course must be completed within one year of purchase.

CPE Sponsor Info : NASBA/QAS #109234. Click here to view specific state approvals.

By the end of the course participants should be able to:

  • Identify the arguments used by the SUMIF function.
  • Identify what you can use in place of the word “TRUE” in VLOOKUP to return an approximate match.
  • Identify which menu in Excel contains the command that launches Excel’s Function Wizard.

Description:
Excel expert David Ringstrom, CPA, introduces several lookup functions, including VLOOKUP, HLOOKUP, MATCH, and CHOOSE, in this valuable presentation. You’ll discover how to use these powerful functions to rapidly develop accurate spreadsheets and look up information, such as pay rates, item prices, and accounting results, versus manually linking to specific cells. David explains the context of when to use lookup functions, demonstrates troubleshooting techniques, and prepares you to deal with subtle issues that can prevent them from working properly. David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

  • Using VLOOKUP to perform approximate matches.
  • Seeing how the HLOOKUP function enables you to perform horizontal matches.
  • Using VLOOKUP to look up data from another workbook.
  • Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
  • Using the MATCH function to find the position of an item in a list.
  • Simplifying multiple-field lookups with concatenation (combining fields together into a single cell).
  • Learning about the MAXIFS function available in certain versions of Excel 2016.
  • Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
  • Troubleshooting other errors VLOOKUP can present such as #REF!, #NAME!, and #VALUE!.
  • Learning why the INDEX and MATCH combination often is superior to VLOOKUP or HLOOKUP.
  • Using the SUMIFS function to sum values based on multiple criteria.
  • Discovering the capabilities of the SUMPRODUCT function.

Delivery Method: Online QAS Self Study.

Level: Basic to Intermediate

Version: David teaches from Excel 2010 in this presentation but every aspect of the material applies equally to Excel 2007, 2013, and 2016.

Prerequisites: A familiarity with Excel spreadsheets

Advanced Preparation: None

AuthorDavid Ringstrom,CPA

Publication: December 2012
Update: April 2018

Format:  On-demand webcast. Powerpoint handout covering video is included as well.
Run-Time: 100 minutes

Passing Grade: 70%

Exam Policies: Exam may be retaken. Course must be completed within one year of purchase.

CPE Sponsor Info : NASBA/QAS #109234. Click here to view specific state approvals.

By the end of the course participants should be able to:

  • Identify the arguments used by the SUMIF function.
  • Identify what you can use in place of the word “TRUE” in VLOOKUP to return an approximate match.
  • Identify which menu in Excel contains the command that launches Excel’s Function Wizard.
  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue

  • Great course! Price was right and the material was very thorough.

    - Alex

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom