Google Sheets | LAMBDA | BYCOL | Function | Create Custom Function | Example | Spreadsheet Tutorial

Use Google Sheets LAMBDA function as one method to create your own function. A reason to use LAMBDA is that you can include more than one formula and or function formula. A LAMBDA can be used as a standalone function, inside a named function, or inside a helper function like BYCOL or BYROW.

Another of the methods to create a custom function is using the Named functions option. The link to the video tutorial on Named functions is given below.

-------------------------------------
How to Use Named Functions in Google Sheets?

Use the Name functions option to create your own function:

   • google sheets named functions create ...  
-------------------------------------
How to Use XMATCH in Google Sheets?

XMATCH returns the position of the specified search key in a row or column:

   • Google Sheets | XMATCH | Function | E...  
-------------------------------------
How to Use INDEX in Google Sheets?

Get to know more about INDEX with this step-by-step video tutorial:

   • Google Sheets INDEX | Use INDEX Googl...  
-------------------------------------
How to Use XLOOKUP for Approximate and Wildcard Match in Google Sheets?

XLOOKUP can return approximate data and supports wildcard search:

   • Google Sheets | XLOOKUP | Function | ...  
-------------------------------------
How to Use XLOOKUP to Extract Data to Left of Search Key in Google Sheets?

XLOOKUP can extract data to left, right, or left and right of search key:

   • Google Sheets | XLOOKUP | Function | ...  
-------------------------------------
How to Use XLOOKUP to Extract Multiple Values in Google Sheets?

XLOOKUP allows to extract multiple values from a row or column:

   • Google Sheets | XLOOKUP | Function | ...  
-------------------------------------
How to Sum Investment Amount by Quarter?

Sum data of investments, profits, revenues, etc., by quarter:

   • Google Sheets | Sum by Quarter | Exam...  
-------------------------------------
How to Sum Investment Amount by Month?

The step-by-step tutorial to sum investment amount (or profits, revenues, expenses, orders, etc.) by month:

   • Google Sheets | Sum Data like Investm...  
-------------------------------------
How to Sum Investment Amount by Week?

The step-by-step tutorial to sum investment amount (or profits, revenues, expenses, orders, etc.) by week:

   • Google Sheets |  How to Sum Investmen...  
-------------------------------------

LAMBDA Function Formula

=LAMBDA([name, …], formula_expression)

Start the formula with an equal-to symbol.

name is an identifier used in the expression. In a LAMBDA function formula, there can be more than one identifier.

formula_expression is the formula, which uses the name(s).

BYCOL Function Formula

=BYCOL(array_or_range, lambda)

Start the formula with an equal-to symbol.

array_or_range is the data range, with the data in each column of the range is used to produce a single value for each column.

lambda is the function, which has the formula that returns a single value for each column.

Examples of LAMBDA Function Formula

Example 1

LAMBDA as a standalone function.

Calculate ROI (Return on Investment) on Shares

ROI= ((Current Market Price-Purchase Price)/Purchase price)*100

The LAMBDA formula to calculate ROI is:

=LAMBDA(cmp, pur_price, ((cmp-pur_price)/pur_price)*100)

Insert 107.5 and 100 as values for cmp and pur_price respectively. The function returns 7.5 as the ROI in percent.

Example 2

LAMBDA inside BYCOL, a helper function.

Calculate the average of numbers in each of the three columns.

=BYCOL(A1:C2,LAMBDA(avg_row, AVERAGE(avg_row)))

Assume cells A1, B1, A2, B2, and A3, B3, each have the following values 100, 200, 300, 400, and 500.

The BYCOL-LAMBDA combo function formula returns the average 150, 350, and 550 for column 1, column 2 and column 3 respectively.

Take a look at this video tutorial, which gives the steps to use the Google Sheets LAMBDA and BYCOL functions with examples.