Beyond Excel's VLOOKUP Function

Author: David H. Ringstrom

In this course, author and Excel expert David H. Ringstrom, CPA, will delve into various advanced techniques and common pitfalls when using the lookup function in Excel. Attendees will learn how to perform exact and approximate matches with VLOOKUP, INDEX/MATCH, and XLOOKUP. You'll see how to resolve and prevent common lookup-related errors such as #N/A and #REF!. You'll see how XLOOKUP can match data from multiple columns, and spill results across multiple columns. David will also show how to use the FILTER function in Excel 2021 and Excel for Microsoft 365 to return multiple results. You'll then see how to use the CHOOSECOLS function in Excel for Microsoft 365 to select which columns that you wish to return from the filtered results.

David H. Ringstrom, CPA, has more than 30 years of experience as a spreadsheet and accounting software consultant and speaker. He has presented over 2,500 live webinars and is the author or co-author of ten books, including “Microsoft 365 Excel All-in-One for Dummies”, “Microsoft 365 Excel for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and “QuickBooks Online for Dummies”.

In his courses, David demonstrates every technique twice: first on a PowerPoint slide with numbered steps and then live in Excel for Microsoft 365 for Windows. He highlights any differences in Excel 2024, 2021, or 2019 during the session and in his detailed handouts. Attendees also receive an Excel workbook containing most of the examples he uses, making it easy to follow along and apply the techniques later. David additionally supports Excel for Mac users by answering their follow-up questions via email.

Publication Date: March 2026

Designed For
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Filtering based upon two or more conditions with the FILTER function in Excel 2021 and Microsoft 365
• Understanding the importance of using IFNA with VLOOKUP versus IFERROR
• Returning multiple columns of data with XLOOKUP from a single formula by using dynamic array functionality
• Looking up data to the left or right of a given column with XLOOKUP
• Identifying situations where VLOOKUP may return #N/A instead of a value
• Displaying subsets of data dynamically by way of the FILTER worksheet function
• Using SUMIFS to total values based on multiple conditions
• Learning what types of user actions can trigger #REF! errors
• Using the OFFSET function to dynamically reference data across different accounting periods
• Matching on two or more columns of criteria at once with XLOOKUP
• Stack different ranges of cells vertically or horizontally with the VSTACK and HSTACK functions
• Using VLOOKUP to perform approximate matches

Learning Objectives

• Define the arguments for the INDEX worksheet function
• Identify the worksheet function that eliminates extraneous spaces from text within a worksheet
• Identify the function that only allows you to look up values horizontally across a row, as opposed to both across rows or down columns

Level
Basic

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Excel 101: Lookup Functions is recommended but not required

Advance Preparation
None