XMATCH Function in Google Sheets (New For 2022!)

The XMATCH function in Google Sheets is a new lookup function in Google Sheets that finds the relative position of a search term within an array or range. It’s an evolution of the original MATCH function.

Here’s a simple XMATCH function that finds the position of the search term “Cho Oyu” in the list of the highest mountains in the world:

=XMATCH(E2,A2:A15)

In the Sheet:

Xmatch Function Google Sheets Simple Example

And here’s how it works:

It looks for the search term from cell E2 (“Cho Oyu”) in the range A2:A15, then returns the position of the search text within this range. Note that the result is relative to the range, irrespective of the row number.

Xmatch Function Google Sheets Explainer

Notice how, unlike a regular MATCH function, you don’t have to specify the “0” search type for an exact match. It chooses the exact match, which is by far the most common use case, by default (in contrast to the MATCH function where you have to add the 0 to explicitly confirm exact matching). More on the search types below.

๐Ÿ”— Get this example and others in the template at the bottom of this article.

Continue reading XMATCH Function in Google Sheets (New For 2022!)

Google Sheets Advent Calendar

This year, I created a Google Sheets Advent Calendar, which you can see in action here:

Google Sheets Advent Calendar

It was a fun project with some interesting techniques, which are explored below.

You could easily modify it for your own example, or use these techniques in different scenarios.

Plus, if you’re too cheap to buy a physical advent calendar, this lets you enjoy the fun of opening a door each day to reveal something, but for free!
Continue reading Google Sheets Advent Calendar

BYCOL Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

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

Hereโ€™s a simple example, showing a table of exam scores:

Bycol Function In Google Sheets

The formula in A8 is:

=BYCOL(A2:D6,LAMBDA(c,AVERAGE(c)))

The easiest way to think about using BYCOL is to think โ€œwhat function can I use on a single column?โ€ and then think of BYCOL as just repeating that operation across multiple columns.

BYCOL passes the input array to a lambda function, which takes an input column, called โ€œcโ€, and calculates the average value for that column. It loops over each column and returns a row array of average values, one for each column.

๐Ÿ”— Get this example and others in the template at the bottom of this article.

Continue reading BYCOL Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

BYROW Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

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, this BYROW function calculates the average score of all three rows in the input array:

=BYROW(A2:D4,LAMBDA(row, AVERAGE(row)))

Which looks like this in our Google Sheet:

byrow Function In Google Sheets

(Of course, you could use three separate AVERAGE functions to perform this calculation.)

But the important thing here is how the BYROW operates. Itโ€™s much more like programming.

We pass an array of data to the BYROW function.

It then passes each row into a lamba function to calculate a single value for that row (the average in this example). The BYROW formula returns these values in a column array, with the same number of rows as the original input array.

๐Ÿ”— Get this example and others in the template at the bottom of this article.

Continue reading BYROW Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

MAKEARRAY Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

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

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

Letโ€™s see a simple example:

=MAKEARRAY(5,3,LAMBDA(row, col, row + col))

This generates an array with 5 rows and 3 columns.

The value of each element in the row is the sum of the row position and the column position, within the array.

So the first value is 2 (row 1 + column 1) and the final value is 8 (row 5 + column 3). The indices are in relation to the position within the array, not the position within the Google Sheet.

Makearray Function Simple Example

๐Ÿ”— Get this example and others in the template at the bottom of this article.

Continue reading MAKEARRAY Function in Google Sheets – LAMBDA Helper Function (New For 2022!)