Over and Above Excel’s VLOOKUP Function -Video

Price: $29.99

CPE Credits: 2.0

Category: , ,

Course Number: VCXLUPDR

beyond lookup functions

Description:
Excel expert David Ringstrom, CPA, explains helpful ways you can improve the integrity of your spreadsheets using Excel’s lookup functions. Most spreadsheet users first learn about VLOOKUP in Excel, so the presentation begins with a brief introduction, and then a comparison of VLOOKUP to the HLOOKUP and LOOKUP functions. He then works through troubleshooting common frustrations with VLOOKUP, such as #N/A and #REF! errors. You’ll see how to future-proof VLOOKUP with Data Validation and the Table feature, as well as learn about alternatives such as MATCH/INDEX, CHOOSE, SUMIF, and SUMIFS.

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.

Topics covered:

  • Enabling VLOOKUP to look up data from the left (instead of only from the right) by using the CHOOSE function.
  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
  • Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
  • Removing the Table feature from a worksheet if it’s no longer needed.
  • Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
  • Using VLOOKUP to perform approximate matches.
  • Learning about the IFNA function available in Excel 2013 and later.
  • Identifying the risks of the LOOKUP function in Excel.
  • Future-proofing VLOOKUP by referencing entire columns when available.
  • Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches.
  • Using the SUMIFS function to sum values based on multiple criteria.
  • Utilizing Excel’s IFERROR function to display alternate values when VLOOKUP returns an error.

Delivery Method: Online QAS Self Study.

Level: Intermediate.

Version: David teaches from Excel 2010 in this session, but covers differences in Excel 2007, 2013, and 2016 where applicable. Excel 2003 is not addressed specifically other than onscreen notes on certain slides.

Prerequisites: Practitioners that have some experience with Excel Lookup Formulas.

Advanced Preparation: None

AuthorDavid Ringstrom,CPA

Publication: July 2018

Format:  On-demand webcast ( video of a webinar)
Run-Time: 1 hr; 41 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:

  • Name what the SUMIFS function returns if a match cannot be found.
  • Describe where the MATCH function can seek for a specified value.
  • Identify the arguments used by the SUMIF function.

Description:
Excel expert David Ringstrom, CPA, explains helpful ways you can improve the integrity of your spreadsheets using Excel’s lookup functions. Most spreadsheet users first learn about VLOOKUP in Excel, so the presentation begins with a brief introduction, and then a comparison of VLOOKUP to the HLOOKUP and LOOKUP functions. He then works through troubleshooting common frustrations with VLOOKUP, such as #N/A and #REF! errors. You’ll see how to future-proof VLOOKUP with Data Validation and the Table feature, as well as learn about alternatives such as MATCH/INDEX, CHOOSE, SUMIF, and SUMIFS.

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.

Topics covered:

  • Enabling VLOOKUP to look up data from the left (instead of only from the right) by using the CHOOSE function.
  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
  • Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
  • Removing the Table feature from a worksheet if it’s no longer needed.
  • Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
  • Using VLOOKUP to perform approximate matches.
  • Learning about the IFNA function available in Excel 2013 and later.
  • Identifying the risks of the LOOKUP function in Excel.
  • Future-proofing VLOOKUP by referencing entire columns when available.
  • Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches.
  • Using the SUMIFS function to sum values based on multiple criteria.
  • Utilizing Excel’s IFERROR function to display alternate values when VLOOKUP returns an error.

Delivery Method: Online QAS Self Study.

Level: Intermediate.

Version: David teaches from Excel 2010 in this session, but covers differences in Excel 2007, 2013, and 2016 where applicable. Excel 2003 is not addressed specifically other than onscreen notes on certain slides.

Prerequisites: Practitioners that have some experience with Excel Lookup Formulas.

Advanced Preparation: None

AuthorDavid Ringstrom,CPA

Publication: July 2018

Format:  On-demand webcast ( video of a webinar)
Run-Time: 1 hr; 41 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:

  • Name what the SUMIFS function returns if a match cannot be found.
  • Describe where the MATCH function can seek for a specified value.
  • Identify the arguments used by the SUMIF function.
  • “Thank you for a high quality CPE resource. As a CPA in a small nonprofit, I find your library quite helpful to meet my needs. I appreciate the service.”- 

    - Cheryl

  • Overall I was very pleased with the course. Previously I knew just enough about pivot tables to be dangerous. Now I see the power that they have and I feel much more confident using pivot tables. The course materials were very clear and well prepared. I’ll be looking at your website for future CPE hours.

    - Scott

  • “Excellent Regulatory Review Course. The self-study format (E-Book) provided an excellent overview of the regulatory requirements for California CPAs. I will be using the E-Book as  a reference material. Thank you for providing a very comprehensive yet affordable option for this required CPE.”

    - Constance