Excel: Lookup Functions in Depth

Description

In this course, you will :

• Learn how to use the new XLOOKUP function, which can handle all of the capabilities of the popular VLOOKUP and HLOOKUP functions while providing more power and flexibility.
• Learn how to match up data using the MATCH function and the newer, more robust XMATCH function, as well as how to extract data using the INDEX function.
• Learn how to restructure the lists being searched so that you can use each function more efficiently, as well as about dynamic array lookup functions such as UNIQUE, SORT, SORTBY, and FILTER.
• shares practical examples that will assist you in understanding how to implement these functions in your workflow

Syllabus :

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

• Locate data with the MATCH function
• Retrieve information by location with the INDEX function
• Use the MATCH and INDEX functions together
• Use the new XMATCH function: Expanded and improved MATCH

 

2. VLOOKUP, HLOOKUP, and XLOOKUP

• Structure data for use with lookup functions
• Find approximate and exact matches with VLOOKUP and HLOOKUP
• Use the new XLOOKUP function for exact matches
• Use the new XLOOKUP function for approximate matches
• New and expanded capabilities using XLOOKUP

 

3. Finding Matching Data without Using External Lists

• Find table-like information within a function using CHOOSE
• Use the SWITCH function for formula-embedded selection

 

4. Other Functions with Lookup Capabilities

• Extract and count unique entries from a list using the UNIQUE function
• Filter data and return matching records with the FILTER function
• Use the SORT and SORTBY functions to create new lists
• Use INDIRECT to return references specified by text strings

Show more