Technical requirements

Dynamic array functions are available in Microsoft 365, Excel 2021, Excel for Web, Excel for iPad, Excel for iPhone, and Excel for Android. These functions return #NAME? in Excel 2019 and earlier, the use of the Spilled Range Operator (#) could result in other errors too, such as #VALUE.

An example workbook that contains all of the formulas used in this chapter can be found on GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter10.

Now that we’ve taken a look at the prerequisites for this chapter, let’s begin with the VLOOKUP and INDEX/MATCH functions, which are often used to look up information in spreadsheets, and see how XLOOKUP enables you to do so much more than you might have previously imagined.