In this presentation, author and Excel expert David H. Ringstrom, CPA, will introduce you to the concept of lookup formulas in Excel. Users not 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 overcome 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 to 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 is the author of “Microsoft Excel 365 for Dummies” and “Exploring Microsoft Excel’s Hidden Treasures” and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019, or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations. 

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetual licensed versions have year numbers in their names and do not receive any feature updates.

Areas Covered:-

• Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
• Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
• Exploring why VLOOKUP sometimes returns #N/A instead of the desired result.
• Incorporating comparison operators within SUMIF to sum numbers based on range criteria, such as greater than, less than, and so on.
• Utilizing Excel’s IFNA function to display alternate values when VLOOKUP returns an error.
• Using the Function Wizard to edit or review existing VLOOKUP formulas.
• Employing the SUMIF function to sum values related to multiple instances of criteria that you specify.
• Learning what types of user actions can trigger #REF! errors.
• Comparing the MIN, SMALL, MAX, and LARGE functions.
• Understanding situations that cause Excel’s VLOOKUP function to return #REF! instead of a value.
• Reconstructing spreadsheet data to use VLOOKUP as a better alternative to nesting IF functions.
• Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365.

Learning Objectives:-

• State the purpose of the column_index_num argument within VLOOKUP.
• 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.

Who Should Attend:-

Professionals seeking to use Microsoft Excel more effectively.

• Excel Users
• Financial Analysts
• Accountants
• CPAs
• Data Professionals.