Wednesday, August 24, 2022

Adding more flexibility to functions in Sheets

What’s changing

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. 


named functions

Who’s impacted 

Admins and end users 


Why it matters 

Named functions provide greater formula flexibility, readability, and reusability within and across Sheets. Formulas that were previously complex and difficult to understand can now be simplified into more comprehensible and reusable named functions. 


This feature also builds upon our recent formula enhancements: intelligent corrections for formulas and suggestions for formulas and functions in Sheets. 


Additional details 

We’re also launching a bundle of new and powerful functions in Sheets: 
  • LAMBDA: Creates and returns a custom function, given a set of names and a formula_expression which uses them. The formula_expression can be calculated by calling the returned function with as many values as the names declared. 
  • Lambda Helper Functions - MAP, REDUCE, BYROW, BYCOL, SCAN, MAKEARRAY: Advanced array-operating functions which accept a reusable lambda as an argument along with an input array(s). 
  • XLOOKUP: Returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match. 
  • XMATCH: Returns the relative position of an item in an array or range that matches a specified value. XMATCH supports enhanced match and search functionality and allows wildcard matches with a question mark (?) or asterisk (*). 

Getting started 

  • Admins: There is no admin control for this feature. 
  • End users: 
    • In Sheets, navigate to Data > Named functions > Add new function > enter details > Next > Create. You can then enter your custom formula into a Sheets’ cell. 
    • Named functions created in a Sheets file are available only in that file. To use named functions in another Sheet, select that file via Data > Named functions > import function > pick the named functions you want to add. 
    • Visit the Help Center to learn more about Named functions. 

Rollout pace 

Availability 

  • Available to all Google Workspace customers, as well as legacy G Suite Basic and Business customers
  • Available to users with personal Google Accounts 

Resources