Last month, Google Sheets released their biggest update in years. For the most part the update is catching up with Excel but their approach to named functions is a much simpler fresher take. I will discuss how to create them, import from other files, some useful ones which I use, pros and cons over Excel’s LAMBDAs, compatibility with Excel and the new functions released alongside it in video, written and screenshot format.

Creating a LAMBDA

The Data menu has a new feature for these named functions, although you can get to it via two other methods too.

You can add a new function from this menu, edit existing ones or import from other files. The more convenient ways to add functions though are:

1. Right click any formula cell More actions > Define named function

2. If you have a particularly complex formula, Google will often suggest it to you, but unfortunately the shortcut shown doesn’t work if it’s not suggested.

These two methods are more convenient as they pull up a placeholder of any linked cells saving a few clicks and allowing you to test it before you write it in the code editor box. You must type out the function name, argument placeholders (except for a rare case with no arguments) and formula definition. The formula definition should reference the predefined placeholders instead of cell references, and if you click on the cell references you can replace them as shown in the second image (or the gif below), then you can click “define” and in the next screen click “create”. The other values are optional but helpful to remind you what the function and each argument do. The Function description on the first screen, and argument description and example on the second, one for each function.

Google provides more explanations of all its functions than Excel, and named functions can have just as many descriptors if the creator wishes. One major drawback in Google compared to Excel is that it (currently) does not support optional arguments. A long workaround is to have conditional if statements (e.g., if the second argument says 0 do nothing otherwise do an extra action).

Importing functions

Google’s functions are stored at the spreadsheet level, rather than on all your files (similar to Excel), but you can import functions from an existing spreadsheet by clicking Data tab > Named functions > Import functions. Sometimes you can get warnings such as the one on the QR_GENERATOR shown.

My top 8 named functions

Here are my top picks for things that should be easier than they are. Note if XLOOKUP wouldn’t have been also released, I probably would have written a similar one with INDEX, MATCH and IFERROR.

1. AGE=DATEDIF(start_date,today(),"Y")
2. IF_CONTAINS=IF(ISERROR(SEARCH(text_to_search,cell_to_look_in)), value_if_false,value_if_true)
3. MONTH_NAME =TEXT(date,"mmm")
4. WEEKDAY_NAME =TEXT(date,"ddd")
5. COUNTERRORS=ARRAYFORMULA(SUM(--ISERROR(range)))
6. TEXTJOINIF=ARRAYFORMULA(TEXTJOIN(", ",TRUE(),IF(Criteria=Criteria_range,Textjoin_range,"")))
7. UNPIVOT= QUERY(ARRAYFORMULA(SPLIT(FLATTEN(rows&"|"&columns&"|"&data),"|")), "Select * where Col3 is not null")
8. QR_GENERATOR= IF(ISBLANK(image_link), "", IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&image_link))

Note that 5 & 6 would need the array formula wrapper to be removed before working in Excel and the last two wouldn’t work in Excel at all. In Excel I would add COUNTUNIQUE and COUNTUNIQUEIF to this list, but they are built into Google Sheets by default.

Excel compatibility

If you export a Google Sheets file as a .xlsx (Click File > Download > xlsx), it keeps the LAMBDA values in the name manager, but it’s not compatible until you make an adjustment. Excel classifies these LAMBDAs as named ranges rather than named functions, so they aren’t functional. To convert it you need to click Formulas > Name manager, click edit, then press OK, you’re not actually making an edit, but this prompts the issue to be fixed in Excel, you need to unfortunately do it one by one. All functions used inside worksheets will give errors until this is amended. Note also that if functions used are not in Excel the file will give you these warnings and not work.

Importing from Excel into Google works better, it correctly converts named ranges and functions to their own place and keeps only the function name, arguments and formula definition, but it adds the characters _xlpm. In front of every argument. As opposed to Excel though, the functions used in the spreadsheets will continue working without amendment. The formula may change slightly as Google will sometimes wrap them inside an ARRAY_CONTRAIN and/or ARRAYFORMULA function as a failsafe, which doesn’t stop them working but can be at times removed manually. This is to do with how arrays work in the two apps. certain array formulas work by default in Excel but in Sheets the equivalents must be wrapped inside an ARRAYFORMULA function (but almost always still function the same way). Here is a screenshot of my functions re-imported.

New functions in Sheets

Together with named functions, Google released nine built in functions - see links below for more description:

• LAMBDA: builds named functions behind the scenes but can be explicitly used in spreadsheets too, particularly with the helper functions below.

The next seven are LAMBDA helper functions (also available in Excel) but are more complex

• BYROW: Applies a LAMBDA to each row to group an array by row
• BYCOL: Similar but for columns
• REDUCE: Applies a LAMBDA to each value in an array to shrinks it to an accumulated result.
• SCAN: Applies a LAMBDA to each value in an array, scans it and returns an array of intermediate values at each step.
• MAKEARRAY: Makes a calculation using a LAMBDA to return an array with user defined dimensions
• MAP: A LAMBDA function can be applied mapping an existing value to a new value for an array

XLOOKUP and XMATCH

Defined by the Excel team as a successor to the iconic VLOOKUP, an XLOOKUP is more powerful than an INDEX MATCH whilst being easier and more intuitive to write than a VLOOKUP. This video compares them in Excel but the same principles apply to Sheets.

• XLOOKUP: This game changer takes three arguments and three more optional ones. =XLOOKUP(search_key, input_range, result_range) can look up a specified value (search_key) in a specified input_range (usually a column) and return the result_range (another column). Some advantages over VLOOKUP include:

a. Being able to look from right to left or left to right
b. Replacing an HLOOKUP by looking top to bottom or bottom to top
c. Its more robust in case columns get added inside the table
d. It defaults to an exact match
e. The fourth input is an optional [missing_value] input substituting the need for an IFERROR
f. The 5th and 6th inputs are used for wildcard matches (using * and ?) and approximate matches.

It still is unable to handle duplicates, do a fuzzy match (e.g. Charles to Charlie) or to look up across multiple columns.

• XMATCH: This is a slightly enhanced version of the MATCH function allows you to have wildcard matching or look from bottom to top instead of top to bottom.
  That concludes the great enhancements to Google Sheets that I have already been using regularly.