Category: Spreadsheets

XMATCH Function in Google Sheets

Lambda Functions 10-Day Challengeđź’ˇ Learn more
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

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

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

Making Google Sheets look less like… Google Sheets

This is a guest post from Josh Cottrell-Schloemer.

Google Sheets is an incredibly powerful spreadsheet tool for pulling, processing, and presenting data. But many people don’t realize that you can also use it to build interactive dashboards.

With a bit of creativity we can go from this:

Dashboard Data Table in Google Sheets

To this:

Google Sheets Dashboard

The skills to build this type of dashboard aren’t difficult to learn and you can get started with a basic knowledge of Google Sheets.

Here’s a walkthrough of the dashboard shown above:


Continue reading Making Google Sheets look less like… Google Sheets

BYCOL Function in Google Sheets – LAMBDA Helper Function

Lambda Functions 10-Day Challengeđź’ˇ Learn more
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

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

BYROW Function in Google Sheets – LAMBDA Helper Function

Lambda Functions 10-Day Challengeđź’ˇ Learn more
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

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