New Functions In Google Sheets For 2022: Named Functions, Lambda, MAP, REDUCE, etc.

Google announced Named Functions and 9 other new functions on 24th August 2022.

The biggest news here is the new feature called Named Functions. Named Functions let you save and name your own custom formulas, built with regular Sheets functions, and then re-use them in other Google Sheet files. It’s a HUGE step toward making formulas reusable.

Let’s look at the named functions and the 9 new functions:

  1. Named Functions
  2. LAMBDA Function
  3. MAP Function
  4. REDUCE Function
  5. MAKEARRAY Function
  6. SCAN Function
  7. BYROW Function
  8. BYCOL Function
  9. XLOOKUP Function
  10. XMATCH Function

1. Named Functions

Named Functions in Google Sheets let you save and name your own custom formulas, using all the built-in functions.

That complex financial formula you created… sure, turn it into a named function called =BENFINANCE(input1,input2,…) and use that instead!

And best of all, you can re-use these named functions in other Google Sheet files.

Here’s an example of a named function I created, called STARCHART, that draws mini star rating charts and can be reused in other Sheets:

Named Functions in Google Sheets Star Chart Example

Learn more about Named Functions

2. LAMBDA Function

The LAMBDA function in Google Sheets creates a custom function with placeholder inputs, instead of the usual A1 type cell or range references.

The main use case for the LAMBDA function is to work with other new lambda helper functions, like MAP, REDUCE, SCAN, MAKEARRAY, BYCOL, and BYROW.

LAMBDA functions are also the underlying technology for Named Functions, which we saw above.

Here’s an example LAMBDA function to calculate percent change:

Lambda Function in Google Sheets Percent Change Formula

In this case though, you’d be better off creating a named function called PERCENTCHANGE rather than creating this lambda function explicitly.

Learn more about the LAMBDA function

3. MAP Function

The MAP function in Google Sheets creates an array of data from an input range, where each value is “mapped” to a new value based on a custom LAMBDA function.

It’s the same idea as the MAP function in programming, a way to loop over an array of data and do something with each element of the array.

I think this formula is going to be super useful!

Here’s how the MAP function works, showing a silly transformation of values into emojis using an IF function as the lambda expression:

Map Function in Google Sheets

Learn more about the MAP Function

4. REDUCE Function

The REDUCE function in Google Sheets operates on an array (like the MAP function). It turns that array input into a single accumulated value, by applying a custom LAMBDA function to each element of the array. I.e. it reduces an array down to a single value.

For example, this simple REDUCE function calculates a cumulative total (yes, using the SUM function is easier, but this reduce example is just for illustration):

Cumulative Reduce Function in Google Sheets

Learn more about the REDUCE Function

5. MAKEARRAY Function

The MAKEARRAY function in Google Sheets generates an array of a specified size, with each value calculated by a custom lambda function.

It’s like the SEQUENCE or RANDARRAY functions, except that in this case a lambda function is applied to each value in the array, so you can generate more complex arrays.

The lamba function has access to the row and column indices for each value.

Here the lambda evaluates the max of the row and column indices and then I added a heat map to that.

Makearray Function in Google Sheets With Heatmap

Learn more about the MAKEARRAY function

6. SCAN Function

The SCAN function in Google Sheets scans an array by applying a LAMBDA function to each value, moving row by row. The output is an array of intermediate values obtained at each step.

The most obvious application to create running totals of your data, like so:

Scan Function Running Total Calculation

Learn more about the SCAN Function

7. BYROW Function

The BYROW function in Google Sheets operates on an array or range and returns a new column array, created by grouping each row to a single value.

The value for each row is obtained by applying a lambda function on that row.

For example, we can use a single BYROW formula to calculate the average score of all three rows in the input array:

Byrow Function In Google Sheets

Learn more about the BYROW function

8. BYCOL Function

The BYCOL function operates in the same way as the BYROW function, but groups each column to a single value and returns a new row array.

In this example, the BYCOL formula outputs a row of average values:

Bycol Function In Google Sheets

Learn more about the BYCOL function

9. XLOOKUP Function

Yes! We have the awesome XLOOKUP function in Google Sheets now!!

It’s a more powerful and flexible version of the VLOOKUP function. It shares some similar capabilities to the INDEX/MATCH combination formulas.

XLOOKUP can lookup to the left, from the bottom up, and even use binary search if you’re working with really large datasets.

Here’s an example of the XLOOKUP performing a leftward lookup:

XLOOKUP Function in Google Sheets Left Lookup

Learn more about the XLOOKUP Function

10. XMATCH Function

Last but not least is the XMATCH function, a more powerful and flexible version of the MATCH function.

It has more matching modes and search options than the plain MATCH function.

Here’s a simple XMATCH example:

Xmatch Function In Google Sheets Simple Example

Learn more about the XMATCH Function

51 thoughts on “New Functions In Google Sheets For 2022: Named Functions, Lambda, MAP, REDUCE, etc.”

  1. Extraordinary! I loved it! Thanks for explaining these new functions I was really waiting for for so long!

  2. Sir I follows all your posted formulas
    I have a question please suggest- For XMATCH , if we have duplicates vales as reference for the same and then what will happen?

  3. Wow, these blur the lines between functions and Scripting! I think they may prove the gateway drug to Scripting for many people. HUGE game changer. Read it here first and happy to Share to the news!

    1. Yes, it’s really exciting. I’m particularly happy about the reusability aspect.

      As you say, the lines are blurring! Once you start writing lambda functions in Sheets, you’re basically already coding 😉

      1. Yes! This is exactly where I’ll be going with this. We’ve basically got a database growing from multiple interconnected sheets & scripts, which is only growing in complexity.

        Looks like these functions will help “simplify” this in many ways, which we desperately need for long term sustainability.
        Cannot wait to start playing around!

  4. Wow! What an update and what an email to wake up to. This takes things to the next level, I’m really looking forward to these new tutorials Ben, thanks for informing 🙂

  5. Great news! I wish they’d added LET too, but named functions are enough to make my day 😉

    Do you know if REFERENCE and SINGLE are now official?

    1. Yes, LET would have been useful, but my guess is they decided named functions was enough.

      Have not heard anything about REFERENCE and SINGLE!

      1. On the face of it the omission of LET is puzzling, given the general trend of Google Sheets to mimic as many of Excel’s functions as possible. However I suspect that the answer here lies with the fact that a plain ‘anonymous’ LAMBDA (i.e. written in a cell and not as a named function) is functionally equivalent to a LET, albeit with different syntax. I wonder if Google Sheets will convert any LETs in imported Excel files automatically on this basis?

  6. In the google sheets I can not see new functions like map, reduce and lamda. Do I need to do something to enable them?

  7. Thank you Ben,

    This has been super helpful and the XLookup function is just fantastic.
    Lambda needs a bit of exploring but it’s great to have it!

  8. Many of these examples (maybe because they are rudimentary) I would currently use ARRAYFORMULA for. Maybe a difference is that ARRAYFORMULA doesn’t accept functions that are geared towards operations on a range (SUM, COUNT, MAX, etc) and it seems like LAMBDA and it siblings will.

    It would be helpful if you could outline possible other differences.

    1. Yes, these examples are fairly simple by design, to help understand the functional concept.

      For sure, there is some overlap between what an array formula and these new lambda functions do, but we’ll see differences emerge as people use them. If you are familiar with programming, then writing these lambda-style functions is more logical and arguably easier than array formulas.

  9. FYI, the initial announcement has been updated by Google and the rollout start date pushed to September 6th, meaning that the features could come as late as September 21st for some accounts.

    I have 2 accounts that got the ability to use lambdas and named functions – albeit without functional helpers such as map / reduce that can be emulated anyway with arrayformulas, and a 3rd account that has no support yet for these new functions (unfortunately it is the workspace of my main customer,).

    1. Thanks for the update, JR! I think they’re trying to speed up the rollout now, so hopefully, folks won’t have to wait as long as Sep 21st.

  10. Hey guys, anyone else still waiting? Got the update to my private account, but not to my workspace business basic yet 🙁

  11. Hi Ben, a specific question for you: do you know any ways of using these new lambdas & helper functions to replicate a SUMIFS that spills as an array? It seems like something that should be possible, but I haven’t been able to figure anything out yet. To me, this is a “holy grail” hope for the lambdas, to be able to “fix” the big gap Sheets has with the various aggregation functions that can’t be made to work with ArrayFormula. SUMIFS is the top of my array wish list for sure.

    1. Woah, I spent some time reading & testing and the answer is yes, MAKEARRAY can be used to turn (probably) any SUMIFS, COUNTIFS, SUM, etc. into an array formula.

      The trick is to turn any single row reference into an INDEX() and use the MAKEARRAY to run the function row by row.

      It’s a bit tough to explain in a comment box, but if your function was SUMIFS( $C$4:$C$10, $A$4:$A$10, A4), ie sum values in column C where the value in column A matches the row’s value. Then the lambda array version would be:
      MAKEARRAY( ROWS($A$4:$A$10),1, LAMBDA(r,c, SUMIFS( $C$4:$C$10, $A$4:$A$10, INDEX($A$4:$A$10,r,c)) ))
      The final A4 reference has become an INDEX for the row and column of the MAKEARRAY to run through, which is passed to the LAMBDA as r & c.

      Sorry, that’s super complex, but it does work, and here’s where it gets good: you can turn that whole MAKEARRAY LAMBDA bit into a Named Function so you just have to feed it a range and all the complex bits inside just work. This is game-changing for sure.

      Ben, I would love to see a blog post about this method in your characteristic clarity. I can’t do it justice here. 🙂

    2. For a real game-changer, turn this into a Named Range and you can now run GOOGLEFINANCE calls with a single array formula:
      =MAKEARRAY(ROWS(range),1, LAMBDA(r,c, GOOGLEFINANCE( INDEX(range,r,c), “price”) ))

  12. Hello Ben!

    I tried using the XMATCH function but it says “Unknown function”. What does one need to do in order to be able to use the function?

    1. I get that on all of these functions, but if I reload the page it works. Not ideal, but I can test the functionality until they load the functions properly into the app.

  13. Named Functions work for me but break when I refresh the page whereafter all the cells with NFs show “Error: Loading data…” forever. I wonder if it’s because I also have Apps Script code and there’s a conflict, i.e. if parts of the GAS code don’t interpret right. Does anyone here know “where” the NF code lives and whether there may be conflicts with sheet-bound apps scripts?

  14. Doing your free 10 Day Challenge course on this one, Is there a way to do SUMIFS inside of an array or by using lambda with helpers?

  15. Wow. After 50 years of spreadsheets, there’s *FINALLY* an elegant solution for creating RUNNING TOTALS!!!

    Thanks for using that as your example , Ben.

    P.S. My first use was to create 2 cells below a list of transfer transactions (i.e. pairs of transactions between banks, each one with a corresponding mate with an amount opposite of the other).

    10.00 Credit Card A
    -10.00 Bank X
    30.00 Credit Card B
    -30.00 Bank X
    40.00 Credit Card B
    -40.00 Bank Y
    =sum(A1:A6) # 0.00
    =reduce(0, A1:A6, lambda(flow, amount, flow = flow + abs(amount)/2) # 80.00

    The sum was $0.00, as it should be, and I used conditional formatting to make it green, indicating that everything balanced.

    But a net of $0.00 doesn’t really give an idea of how much flowed backforth, so I figured each side contributes 1/2 to to the financial flux, and added it up! In this case, $80 flowed through the “pipe”.

Leave a Reply

Your email address will not be published. Required fields are marked *