In this practical webinar, you will learn helpful tweaks from Excel expert, David Ringstrom, CPA, that you can use with the noteworthy VLOOKUP function. Many Excel users rely on VLOOKUP to return data from other locations in a worksheet. However, using VLOOKUP isn’t always the most efficient approach. Among other things, you will learn alternatives to use, including the INDEX and MATCH, SUMIFS, SUMPRODUCT, IFNA, and OFFSET functions.
You will see each technique demonstrated at least twice: 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 presentation, as well as in his detailed handouts. You will also receive an Excel workbook that includes most of the examples used during the webcast.
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.
You will learn how to:
- Eliminate inputs that could cause VLOOKUP to return #N/A with Data Validation
- Transform numbers stored as text into values by way of the Text to Columns wizard
- Identify the risks of the LOOKUP function in Excel
- Utilize Excel’s IFERROR function to display alternate values when VLOOKUP returns an error
- Use VLOOKUP to perform approximate matches
- Enable VLOOKUP to look up data from the left (instead of only from the right) by using the CHOOSE function
- Use the SUMIFS function to sum values based on multiple criteria
- Future-proof VLOOKUP by using Excel’s Table feature versus referencing static ranges
- Remove the Table feature from a worksheet if it’s no longer needed
- Avoid the complexity of nested IF statements with Excel’s CHOOSE function
Upon course completion, you will be able to:
- Identify what you can use in place of the word TRUE in VLOOKUP to return an approximate match
- Recall which menu contains the Text to Columns wizard
- Define the arguments for the INDEX worksheet function
Suggested Audience: Practitioners who wish to learn more about utilizing Excel’s lookup functions to work more efficiently.
Prerequisites: None. Familiarity with Excel Lookup Functions Recommended.
Advance Preparation: None
Program Level: Intermediate
Field of Study: Computer & Software Applications (2 hours)
Credit Type: Self-Study (Please note that to receive Self-Study credit you will need to complete a final exam after viewing the webinar.)