Excel 101: Lookup Formulas (Completed)

In this presentation, author and Excel expert David H. Ringstrom, CPA, will introduce you to the concept of lookup formulas in Excel. Users that aren't using functions such as VLOOKUP, XLOOKUP, SUMIF and so on must always create formulas that directly reference specific cells instead of enabling Excel to retrieve data for them. Learn how to address common formula issues, such as the #N/A error and overcoming unwanted automatic text corrections that can interfere with lookups. Discover how extraneous spaces can trigger #N/A in lookup functions, and why the IFNA function is better to use than IFERROR with VLOOKUP. Stay ahead of the curve with the XLOOKUP function in Excel 2021 and Excel for Microsoft 365, including its If_Not_Found argument. Gain insights into SUMIF, HLOOKUP, and other types of lookup functions. Unlock the potential of looking up data from a second workbook and explore VLOOKUP's use in approximate matches. Lastly, explore the alternative uses of VLOOKUP as an alternative the IF function for approximate matches, and learn how to utilize MIN/MAX and SMALL/LARGE functions effectively. Don't miss out on this opportunity to improve your Excel skills.

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 webinars, David encourages questions throughout the presentation and 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.

Who Should Attend
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Using VLOOKUP to look up data from another workbook
• Avoiding lookup failures caused by sneaky spaces and subtle typos
• Replacing direct cell references with lookup functions such as XLOOKUP
• Learning what types of user actions can trigger #REF! errors
• Implementing Data Validation to ensure users make choices that VLOOKUP will recognize as valid
• Simplifying multiple-field lookups with concatenation (combining fields into a single cell)
• Comparing the MIN, SMALL, MAX, and LARGE functions
• Pairing the MATCH and INDEX functions as an alternative to VLOOKUP or XLOOKUP
• Handing #N/A errors within XLOOKUP
• Exploring the nuances of wildcard lookups with XLOOKUP
• Using the HLOOKUP function to look horizontally across rows in any version of Excel

Learning Objectives

• State the column that a VLOOKUP function would return data from when a match is found on the lookup_value argument
• Identify the data integrity improvement that the Table feature adds worksheet functions such as VLOOKUP
• Differentiate between IFNA and IFERROR in handling Excel formula errors

Level
Basic

Instructional Method
Group: Internet-based

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

Program Prerequisites
None

Advance Preparation
None