Excel 365: Use Advanced Lookup Functions

Course description

This course introduces advanced lookup techniques in Excel 365 that empower users to work with dynamic, flexible data retrieval methods. It begins with the OFFSET function, which allows for position-based referencing rather than fixed cell references. Learners see how OFFSET can be used to establish variable ranges, providing flexibility in calculations that adapt to structural changes in a worksheet, such as column deletions or dropdown-controlled ranges. The course then covers the use of VLOOKUP with multiple criteria by combining values using the CONCAT function to form unique identifiers for lookup operations. It also explores how to integrate wildcards into VLOOKUP formulas, enabling users to search for partial matches when dealing with composite or embedded data. Additional functions featured include FORMULATEXT for displaying the text of a formula within a cell and ISFORMULA to determine whether a cell contains a formula or static content. The IFNA function is introduced as a streamlined way to handle NA errors specifically, allowing users to customize outputs for missing values. Learners also explore XLOOKUP’s ability to handle multiple lookup criteria and return arrays, making it ideal for nuanced searches such as filtering by last name and department. The course concludes with XMATCH, a modern replacement for the traditional MATCH function, which offers enhanced search and match modes. These techniques provide users with robust tools for advanced data lookup and error management.

Course outline