10

Lookup and Dynamic Array Functions

In this chapter, we’ll discuss two types of worksheet functions that will literally transform your workbooks. Lookup functions have long allowed us to create formulas that can retrieve data from elsewhere in a spreadsheet without requiring us to connect directly to the individual cells. Dynamic array functions are a quantum leap forward, enabling us to create single formulas that can return results to multiple cells. The new SORT, FILTER, and UNIQUE functions enable us to automate tasks that, previously, always had to be carried out by hand. The new XLOOKUP function is both a lookup function and a dynamic array function that is a modern replacement for both VLOOKUP and MATCH/INDEX. As you’ll discover, dynamic ...

Get Exploring Microsoft Excel's Hidden Treasures now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.