Google Sheets adds XLOOKUP, Named Functions, and a few other advanced features

Google Sheets is giving power users a boost this week, as the Excel alternative has just picked up XLOOKUP and several other long-missing features.

Ben Collins, a Google Developer Expert working with Sheets, shared this week an explanation detailing several new features that have been released for Google Sheets. Specifically, 10 new functions that will be helpful for power users.

Google further announced the feature in a blog post, explaining:

We’re introducing named functions, a new feature that allows you to create high-performance custom functions that support built-in Sheets formula constructs. To maintain reusability across files, you can also import named functions from existing Sheets files, allowing you to use functions created in one Sheets file in a different Sheets file.

The new features include Named Functions, which allows users to save and rename custom functions to reuse them later in the same or other Google Sheets files. In a similar vein, the new LAMBDA function can create custom, reusable functions with placeholder inputs – not to be confused with LaMDA, Google’s natural language AI.

One of the biggest new additions is support for XLOOKUP and XMATCH functions in Google Sheets, which have been a glaring omission for some time. These functions allow for spreadsheets to surface data with a search, as seen below in Collins’ demo.

Beyond those powerful features, Google Sheets has also added a handful of other advanced features for spreadsheet users. Those include:

• MAP: Data from input range is “mapped” to a new value
• REDUCE: Turn input array into a single accumulated value
• MAKEARRAY: Similar to SEQUENCE, uses LAMBDA to generate an array of specific sizes
• SCAN: Scans array and applies LAMBDA function to each value, row by row
• BYROW/BYCOL: Operates on a range and returns new column or row by grouping each row/column into a single value

Google is rolling out these new functions starting this week and over the next two weeks for all users.

More on Workspace:

• How to merge cells in Google Sheets on desktop or mobile
• Google Docs, Sheets, and Slides now offer individual control over accessibility settings
• How to alphabetize data in Google Sheets