Excel: Lookup Functions in Depth

Course details

Leverage the power of Excel's lookup functions with expert Dennis Taylor. This course demystifies key lookup functions, enables you to skillfully retrieve and manipulate data, and empowers you to streamline your workflow and tackle complex data analysis tasks with confidence. Explore ways to optimize your data structures for efficient lookups and examine practical examples to integrate these functions into your workflow. Whether you're new to lookup functions or looking to upgrade your skills, this course will enhance your Excel proficiency and streamline your data analysis tasks.

Learning objectives
Implement the XLOOKUP function to efficiently retrieve data, demonstrating its advantages over VLOOKUP and HLOOKUP in various scenarios.
Apply MATCH and XMATCH functions to accurately locate and compare data within large datasets, enhancing your ability to analyze complex spreadsheets.
Utilize the INDEX function in combination with other lookup functions to extract specific data points from multi-dimensional arrays or tables.
Employ dynamic array functions (UNIQUE, SORT, SORTBY, and FILTER) to manipulate and analyze data sets, streamlining your data processing workflows in Excel.

Concepts

0. Introduction

01 - Transform your Excel skills with in-depth lookup functions

1. Locating Data with MATCH, INDEX, and XMATCH Functions

02 - Locating data with the MATCH and XMATCH functions
03 - Retrieving information by location with the INDEX function
04 - Using the XMATCH and INDEX functions together

2. VLOOKUP, HLOOKUP, and XLOOKUP

05 - Structuring data for use with lookup functions
06 - Finding exact and approximate matches with VLOOKUP and HLOOKUP
07 - Using the XLOOKUP function for exact matches
08 - Using the XLOOKUP function for approximate matches
09 - New and expanded capabilities using XLOOKUP

3. Finding Matching Data Without Using External Lists

10 - Finding table-like information within a function using the CHOOSE function
11 - Using the SWITCH function for formula-embedded selection

4. Other Functions with Lookup Capabilities

12 - Extracting and counting unique entries using the UNIQUE function
13 - Using FILTER and CHOOSECOLS for multiple column retrieval
14 - Use the SORT and SORTBY functions to create new lists
15 - Use the INDIRECT function to return references specified by text strings

5. Recently Introduced Functions with Lookup Capabilities

16 - CHOOSEROWS and CHOOSECOLS - Return lists with selected rows or columns
17 - TAKE and DROP - Copy from lists selecting or ignoring consecutive rows or columns
18 - TOROW and TOCOL - Convert array data into a single row or column
19 - WRAPROWS and WRAPCOLS - Convert row or column data into an array
20 - VSTACK and HSTACK - Stack multiple arrays into a vertical or horizontal array

Conclusion

21 - Your next steps in Excel

Excel: Lookup Functions in Depth

Authors

Dennis Taylor