Skip to main content

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 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:

FTC: We use income earning auto affiliate links. More.

You’re reading 9to5Google — experts who break news about Google and its surrounding ecosystem, day after day. Be sure to check out our homepage for all the latest news, and follow 9to5Google on Twitter, Facebook, and LinkedIn to stay in the loop. Don’t know where to start? Check out our exclusive stories, reviews, how-tos, and subscribe to our YouTube channel

Comments

Author

Avatar for Ben Schoon Ben Schoon

Ben is a Senior Editor for 9to5Google.

Find him on Twitter @NexusBen. Send tips to schoon@9to5g.com or encrypted to benschoon@protonmail.com.