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 array functions can vastly improve spreadsheet integrity by creating self-resizing schedules, such as a dynamic amortization schedule. Amortization schedules are used to document the interest, principal, and running balance over...