Hi Reader,

Welcome to the Google Sheets Tips newsletter #233!

➜ News

I.
​A tasty update to drop-down menus in Google Sheets. This is a very welcome upgrade, as drop-down menus have historically been a clunky afterthought, buried under data validation. Now, they get their own space and a visual overhaul. This is rolling out over the next few weeks to everyone.

​Read more about the new drop-downs​

II.
​I finally found time to publish the last blog post in the series about the new 2022 Google Sheet functions.

The XMATCH function is an upgrade to the original MATCH function and is really useful for lookup type formulas. Learn more:

​XMATCH Function in Google Sheets​

(Note, this is also covered in day 9 of my free lambda functions course.)

_______

➜ Google Sheets Tip #233: How to categorize data with IFS

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

One of the best formulas that Google introduced in recent years is the IFS function, which extends the mighty IF function and lets you use multiple conditional tests.

It's incredibly useful for categorizing data. In this example, we'll see how.

Suppose we want to group transaction values into small, medium, or large, so that we can look at each group in isolation.

In your work, these groups could be anything you choose: priority levels, risk levels, flood stages, etc.

Let's see the formula with the data, then break it down:

And here is the formula:

You can see the formula classifies rows into small, medium or large, based on the value in column 2.

Anatomy of IFS

The IFS function consists of pairs of conditional tests and results.

Think of a plain IF function first. You test something (is the value in cell A1 greater than 10, for example), then have an output for when the test was true, and an output when the test fails.

With IFS, you have the test first, then the output for when the test is true. Then, if the test fails, you go to the next pair of test and value.

Let's see our example again:

Pair 1

It checks if B2 is blank, then leaves the result blank if true. I've set the result blank so it will just return an empty cell.

Although you could omit this first test, it's handy if you want to convert this to an array formula later (more on this below).

Pair 2

If B2 is greater than 1 million, put the text "Large" in the formula output cell (C2).

This test is only reached when the first test fails, i.e. the cell B2 is not blank.

Pair 3

This test is only reached if the first two tests fail. I.e. it's not blank and the number is not greater than 1 million.

Then, this test checks if it's greater than 500,000 and if so, outputs the value "Medium".

Pair 4

Unlike a regular IF function, there is not a final false output. Should all the tests in an IFS fail, it outputs an #N/A error.

So, I prefer to add a final test pair where the test is just the value TRUE, which obviously is always true.

So this test works as a catch-all, and will output the value "Small" in this case.

Conversion to Array Formula

If you have a lot of data, or data that is constantly expanding (e.g. Google Form data), then an array formula can be super handy. It will calculate automatically for any new rows that are added to your dataset.

We convert the IFS formula above to an array formula by extending the input range to the full column and adding the array wrapper, like so:

Note, with the addition of the new LAMBDA formulas, you can also write this with the BYROW function:

Personally, I love these new LAMBDA style formulas. I find them more intuitive and cleaner than the array formulas.

But, when testing with 75,000 rows of data, the array version appeared to be slightly quicker at calculating. This is merely an observation though, not a scientific test, so I'm not claiming it's necessarily always the case. More research is required!

As always, I look forward to hearing any thoughts you have on the matter...

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Moving a snail from the road to the grass...​