Excel Accountant: Intro to Lookup Functions

Welcome to myCPE

Learning Objectives

To explain the purpose of the column_index_num argument in the VLOOKUP function.
To analyze and describe the arguments utilized in the SUMIF function.
To identify the enhancement in data integrity facilitated by the Table feature in VLOOKUP.
To discuss how to apply functions like MIN, SMALL, MAX, and LARGE for comparative analysis in Excel.
To analyze and troubleshoot common errors, such as #REF!, #NAME!, and #VALUE!, associated with the VLOOKUP function in Excel.

Course Description

In this online CPE course, David introduces you to several lookup functions, including VLOOKUP, HLOOKUP, MATCH, and CHOOSE. These powerful Excel functions allow you to rapidly develop accurate spreadsheets and look up information, such as pay rates, item prices, and accounting results, versus manually linking to specific cells. Step by step, 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.

Excel expert David Ringstrom, CPA, demonstrates every technique at least twice in this online continuing education course: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the online CPE course 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:

Comparing the MIN, SMALL, MAX, and LARGE functions.
Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
Exploring why VLOOKUP sometimes returns #N/A instead of a desired result.
Future-proofing VLOOKUP by referencing entire columns when available.
Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
Implementing Data Validation to ensure users make choices that VLOOKUP will recognize as valid.
Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
Learning what types of user actions can trigger #REF! errors.
Reconstruct spreadsheet data to use VLOOKUP as a better alternative to nesting IF functions.
Simplifying multiple-field lookups with concatenation (combining fields into a single cell).
Troubleshooting other errors VLOOKUP can present, such as #REF!, #NAME!, and #VALUE!.
Understanding situations that cause Excel’s VLOOKUP function to return #REF! instead of a value.

Office 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Click here for more CPE/CPD webinars on Excel.

Overview

Addresses

2 mins
Function Wizard

5 mins
Lookup_Value

11 mins
Address - Formula

20 mins
Correcting #REF! Errors in VLOOKUP

57 mins
Viewing Two Worksheets at Once

68 mins
Price Look-Up

79 mins
Tax Rates

90 mins
Do You Have Office 365?

94 mins
MINIFS Function (Excel 2019+)

97 mins

Webinar Qualifies For

2 CPE Credit of Computer Software and Applications for Certified Public Accountants (CPA-US)
2 General Credit of Computer Software and Applications for Accountant/Bookkeeper/Tax Professionals
2 CPD Credit of Computer Software and Applications for CPA in Canada
2 CPD Credit for Association of Chartered Certified Accountants (ACCA)
2 CPD Credit for Chartered Accountant - ICAEW
2 CE Credit of Information Technology (inc. Excel) for Certified FP&A Professional (FP&A)
2 CPD Credit for Chartered Accountants - Scotland
2 CPE Credit of Computer Software and Applications for Iowa Licensed Public Accountant (IA-LPA)
2 CPD Credit for Chartered Accountants - Ireland (CAI)
2 CPD Credit for CPA - Ireland
2 CPE Credit of Computer Software and Applications for Maine Licensed Public Accountant (ME-LPA)
2 CPE Credit of Computer Software and Applications for Delaware Licensed Public Accountant (DE-LPA)

About Instructor

David Ringstrom, CPA (US)

President, EXCEL Accountant

I own an Atlanta-based training and consulting firm that helps businesses nationwide streamline accounting challenges. Our specialties include:

Microsoft Excel
Microsoft Access
Sage 50/Peachtree Accounting
QuickBooks

Our services include training, technical support, accounting automation, and problem solving. I have spoken at conferences or meetings for these organizations:

National Association of Certified Valuators and Analysts
Institute of Real Estate Managers
Georgia Society of CPAs
South Carolina Society of CPAs
Georgia Government Finance Officers Association
Atlanta chapter, American Society of Women CPAs
Perimeter Chapter, Georgia Society of CPAs

I teach over 200 live webinars annually on Microsoft Excel across roughly two-dozen training providers. I am available for in-house training or select conference engagements as well.

I am the co-author of Idiot's Guide to Introductory Accounting. I've published hundreds of freelance articles on various topics, including Lotus 1-2-3, Quattro Pro, Microsoft Excel, Microsoft Office Accounting, QuickBooks, Peachtree Accounting, and more.

I serve as the technical editor for several books in Wiley's Dummies series, including QuickBooks for Dummies, Quicken for Dummies, Peachtree for Dummies, and more.

Specialties: Microsoft Excel, Visual Basic for Applications, Sage 50, Peachtree Accounting, QuickBooks, Microsoft Office Accounting, freelance writing, training, business consulting

Additional Information

Duration

2 Hours

Course Level

Basic

Instructional Method

QAS Self Study

Pre-requisites

None

Advance Preparation

None

FAQ’s

Click Here to View

NASBA APPROVED

MY-CPE LLC, 1600 Highway 6 south, suite 250, sugar land, TX, 77478

MY-CPE LLC (Sponsor Id#: 143597) is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.NASBARegistry.org.

Ratings and Review

4.6

Excellent

Very Good

Average

Poor

Terrible

Dana Hilton, CGA, CPA (US), Others, QB Pro Advisor

Presenter did an excellent job of providing information as well as example of each step

Adam Schneider, CPA (US)

Great for learning vlookups and other helpful tips

Joseph Mason, CPA (US)

Good presentation. Good reference materials.

Rebecca Cummings, Others

Good content. It would have been easier to follow as an actual walk-thru in Excel. The slides with information and numbered steps were not easy to follow, in my opinion.