Webinar Details $199
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Taxation and Accounting, Software
- Credit: CPE 2.00, ATATX 1.50
Expand your Microsoft Excel toolbox in this intermediate-level webinar focused on mastering modern lookup functions and powerful data transformation tools. You’ll learn how to compare the dynamic INDEX/MATCH combination with the versatile XLOOKUP function, specifically within Excel 2021 and Excel for Microsoft 365. Discover when to use each approach to efficiently retrieve, analyze, and manage data in your spreadsheets.
Led by author and Excel expert David Ringstrom, CPA, this session also dives into advanced features like Power Query and Solver. You’ll see how to clean up messy reports exported from accounting systems and other platforms—removing issues like merged cells, blank rows, and inconsistent formats. Plus, David will demonstrate how to use Solver to identify combinations of numbers that match a specific total, such as invoices or payments. Every concept is shown twice—first in a step-by-step PowerPoint and then live in Excel—ensuring you leave with actionable, repeatable techniques. Attendees will also receive detailed handouts, including an Excel workbook with many of the examples covered.
Topics Typically Covered:- Understanding how VLOOKUP retrieves data without manually referencing individual cells
- Using XLOOKUP to find the last match in a list instead of the first
- Exploring the MATCH function to locate the position of items in a list
- Comparing INDEX/MATCH to VLOOKUP and HLOOKUP for flexible lookups
- Cleaning up data exports with Power Query by resolving blank rows, merged cells, and missing entries
- Diagnosing #N/A errors from inconsistent formatting or hidden characters
- Leveraging Solver to determine which transactions sum to a target amount
- Enabling Solver add-in for advanced what-if analysis scenarios
- Navigating XLOOKUP’s advantages in Excel 2021 and Microsoft 365
- Explore powerful lookup tools by comparing INDEX/MATCH and XLOOKUP, and learn when each method offers the best results.
- Master data cleanup techniques in Power Query to transform reports into usable, analysis-ready formats quickly.
- Utilize Excel's Solver tool to perform in-depth numerical analysis and match totals across lists of values.
By attending this session, you’ll gain practical Excel strategies that improve your workflow, reduce manual errors, and save time on data preparation and analysis.
Who Should Attend:Professionals who want to enhance their effectiveness and efficiency with Microsoft Excel, especially in accounting, finance, or data analysis roles.
CPE Credit Information: Level: Intermediate
Format: Live webcast
Instructional Method: Group Internet-based
NASBA Field of Study: Specialized Knowledge and Applications (2 hours)
Program Prerequisites: Prior experience with Microsoft Excel is recommended
Advance Preparation: None
- Introduction
- Topics At A Glance 00:01:34
- Presenting with Microsoft 365 for Windows 00:05:40
- Section 1: Exploring Excel Lookup Functions 00:07:31
- Navigating with VLOOKUP 00:09:23
- Pairing INDEX and MATCH 00:17:40
- Comparing XLOOKUP to Older Lookup Tools (2021+) 00:26:58
- Fixing #N/A Errors in XLOOKUP 00:35:17
- Looking Up Data From Two Or More Columns At Once 00:44:21
- Section 2: Transforming and Filtering Data With Power Query 00:48:55
- Transforming Data With Power Query (Source Data) 00:49:52
- Transforming Data With Power Query (1/5) 00:52:43
- Transforming Data With Power Query (2/5) 00:59:27
- Transforming Data With Power Query (3/5) 01:04:35
- Transforming Data With Power Query (4/5) 01:08:25
- Transforming Data With Power Query (5/5) 01:11:48
- Section 3: Solving For Sums With Excel’s Solver Add-In 01:19:29
- Enabling Excel's Solver Add-In 01:20:24
- Using Solver to Match a Target Sum (1/6) 01:27:30
- Using Solver to Match a Target Sum (2/6) 01:31:07
- Using Solver to Match a Target Sum (3/6) 01:31:09
- Using Solver to Match a Target Sum (4/6) 01:32:23
- Using Solver to Match a Target Sum (5/6) 01:34:29
- Using Solver to Match a Target Sum (6/6) 01:34:39
- Section 4: Protecting And Recovering Your Work 01:35:56
- Backing Up Key Workbooks Locally 01:37:00
- AutoSaving Workbooks with OneDrive 01:39:17
- Thank You for Attending 01:41:28
- Presentation Closing 01:42:11
-
David H. Ringstrom, CPA
David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]
CPE Credit
Aurora Training Advantage 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.
For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.
You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.
ATATX Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.- #N/A Error 00:08:56, 00:28:24, 00:35:22
- Add-In 01:20:42
- AutoSave 01:39:25
- Cell 00:09:29, 00:28:45, 00:32:45, 00:40:53, 00:50:10, 01:32:26
- Cell Reference 00:09:31, 01:32:43
- Column 00:10:05, 00:11:35, 00:15:21, 00:18:10, 00:28:05, 01:06:08, 01:13:40
- Dialog Box 00:54:34, 01:20:51, 01:37:50
- Direct References 00:28:40
- Format 01:12:23, 01:13:32
- Formula 00:09:27, 00:36:06
- INDEX Function 00:08:15, 00:17:56
- ISNUMBER 00:42:27
- LEN Function 00:41:38
- LOOKUP 00:01:39, 00:07:39, 00:26:54, 00:40:41
- MATCH Function 00:08:15, 00:17:55, 00:27:17, 00:46:04
- Microsoft 365 00:06:55
- Pivot Table 00:02:20
- Power Query 00:02:05, 00:49:04, 00:53:42, 01:06:05, 01:15:41
- Power Query Editor 00:55:10, 00:59:27
- Row 00:15:21, 00:18:52, 00:50:06
- Solver 00:02:31, 01:19:48, 01:27:50, 01:35:04
- Spreadsheet 00:07:53
- SUBTOTAL 01:34:46
- SUM 01:29:29
- Table 00:11:00
- Table Array 00:10:27, 00:11:42, 00:17:26, 00:29:01
- Transaction 00:02:49
- VLOOKUP 00:01:48, 00:07:40, 00:10:14, 00:13:24, 00:28:53, 00:46:04
- What-If Analysis 01:19:51
- Workbook 00:53:13, 01:37:39
- Worksheet 00:55:01, 00:57:59, 01:05:49, 01:14:53
- XLOOKUP 00:01:55, 00:07:12, 00:26:55, 00:32:03, 00:44:31
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
Add-In: An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.
AutoSave: Excel AutoSave is a tool that automatically saves a new document that you've just created, but haven't saved yet. It helps you not to lose important data in case of a computer crash or power failure.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Direct References: Direct cell referencing is a method of passing the value of one cell as an argument in a linkage function of another cell. By directly referencing an Excel cell number, you can streamline the link creation process and avoid manually building or modifying each link.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Formula: A formula is an expression which calculates the value of a cell.
INDEX Function: The INDEX function can be used to return data from within a given range based on a row and/or column number that you specify.
ISNUMBER : Use the ISNUMBER function to check if a value is a number. ISNUMBER will return TRUE when value is numeric and FALSE when not.
LEN Function: The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
LOOKUP: The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.
MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Power Query Editor: Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.
Solver: Solver is an add-in for Microsoft Excel that allows you to perform what-if analysis operations. Excel's Goal Seek feature allows you to solve for a single input, while Solver allows you to solve for a single input while optionally placing constraints additional cells during the solving process.
Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
Table Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.
Transaction: In QuickBooks, a transaction type identifies what kind of transaction occurred, such as a customer transaction, bill payment or a bank transfer. When you submit a transaction, you type in a transaction code to represent it.
VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.
What-If Analysis: What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables. Scenarios and Data tables take sets of input values and determine possible results.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.
