Excel: Lookup Functions in Depth (2020)
1h 9mIntermediate2020-09-11
Authors
Dennis Taylor
Excel expert with 25+ years of experience
Course details
Microsoft Excel offers a variety of powerful lookup functions that allow you to easily retrieve information across spreadsheets. In this course, Excel expert Dennis Taylor demystifies some of the most significant lookup functions. Tune in to learn how to use the new XLOOKUP function, which can handle all the capabilities of the widely used VLOOKUP and HLOOKUP functions, in addition to providing more power and flexibility. Explore uses of the MATCH function and the newer, more robust XMATCH function to match up data, and learn how to extract data using the INDEX function. Discover how to restructure the lists being searched to ensure that you're using each function efficiently, and learn about dynamic array lookup functions like UNIQUE, SORT, SORTBY, and FILTER. Along the way, Dennis shares practical examples that help you grasp how to implement these functions in your workflow.
Skills covered
Microsoft OfficeOffice 365SpreadsheetsMicrosoft ExcelData AnalysisData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftDeep Dive (X:Y)
Concepts
0. Introduction
- 01 - Introduction
1. Locating Data with MATCH, INDEX, and XMATCH Functions
- 02 - Locate data with the MATCH function
- 03 - Retrieve information by location with the INDEX function
- 04 - Use the MATCH and INDEX functions together
- 05 - Use the new XMATCH function - Expanded and improved MATCH
2. VLOOKUP, HLOOKUP, and XLOOKUP
- 06 - Structure data for use with lookup functions
- 07 - Find approximate and exact matches with VLOOKUP and HLOOKUP
- 08 - Use the new XLOOKUP function for exact matches
- 09 - Use the new XLOOKUP function for approximate matches
- 10 - New and expanded capabilities using XLOOKUP
3. Finding Matching Data without Using External Lists
- 11 - Find table-like information within a function using CHOOSE
- 12 - Use the SWITCH function for formula-embedded selection
4. Other Functions with Lookup Capabilities
- 13 - Extract and count unique entries from a list using the UNIQUE function
- 14 - Filter data and return matching records with the FILTER function
- 15 - Use the SORT and SORTBY functions to create new lists
- 16 - Use INDIRECT to return references specified by text strings
Conclusion
- 17 - Next steps