How to Use Isbetween with Filter Formula in Google Sheets

With my examples below, you can learn two types of the use of the Isbetween with Filter in Google Sheets.

I think I am one of the early takers of the Isbetween Operator function in Google Sheets. It has already a subject of a few of my tutorials in the past.

This time I’m going to elaborate on the possibility of using the Isbetween with the Filter function in Google Sheets.

Until recently, we were using a few operators such as >, >=, <, or <= to filter data that falls within two dates/numbers or dates/numbers given in two specific arrays.

Lately, if we wish, we can replace those operators with the Isbetween function within the Filter formula in Google Sheets.

I don’t know whether using this new function within the Filter formula is going to make an impact on your sheet or not.

Because I am not aware of which method (operators or function) is more resource-friendly. I couldn’t find any official documentation on this.

But I am sure about one thing, that if you know the usage, you won’t be confused seeing such a formula in a shared sheet.

Isbetween with Filter Formula Examples

The Filter function has two arguments, and they are range and condition. We will use the Isbetween with Filter as the conditions/criteria.

In this case, we can mainly use conditions in two ways – criteria as two values or two arrays. You can find the details under examples 1 and 2 below, respectively.

Example 1: Two Values as the Criteria

In the below sample data in B3:D, I want to filter the “Current Cost” wherever the “Purchase Date” falls between the two dates provided in cells G3 and H3. I want to consider one more condition, that is “Stock”.

Isbetween with Filter Formula - Example 1

Let’s ignore the criteria “Stock” for the time being. Here are the formulas that we can use.

=FILTER(D3:D,isbetween(C3:C,G3,H3))
=FILTER(D3:D,C3:C>=G3,C3:C<=H3)

The above two formulas filter the “Current Cost” if the “Purchase Date” falls between 01-Apr-2020 and 31-Mar-2021 (both the dates inclusive).

To filter only a specific “Stock”, here “A”, the formula to use will be as follows.

=FILTER(D3:D,isbetween(C3:C,G3,H3),B3:B=F3)
=FILTER(D3:D,C3:C>=G3,C3:C<=H3,B3:B=F3)

What about adding one more stock below?

Isbetween with Filter Formula - Example 2

Now we require to follow the OR in Filter as below (there is a Regex option too).

=FILTER(D3:D,isbetween(C3:C,G3,H3),(B3:B=F3)+(B3:B=F4))
=FILTER(D3:D,C3:C>=G3,C3:C<=H3,(B3:B=F3)+(B3:B=F4))

Related: Regexmatch in Filter Criteria in Google Sheets [Examples]

You should make note of the below points, when you use the Isbetween with Filter Formula as above in Google Sheets.

Notes:-

  1. You must try the above formulas in a blank column, as it may return an array (multiple rows) result. Otherwise, you may see the #REF error.
  2. To make both the dates in the criteria exclusive (now it is inclusive in all the formulas), you must modify all the Isbetween within the Filter formulas as isbetween(C3:C,G3,H3,FALSE,FALSE). Regarding the operator based ones, replace >= with > and <= with <.

You May Like: Filter by Date Range Using Filter Menu in Google Sheets.

Example 2: Arrays as the Criteria When Using Isbetween with Filter in Google Sheets

In all the above Isbetween with Filter Formula examples, the criteria are two dates. Here it will be in two columns and also part of the data.

In other words, how to filter when the values in one array are falling between the values in two other arrays. This sample data may explain better.

Criteria in Two Arrays - Example 1

I have explained such type of Isbetween use in my tutorial here – Find Whether Test Results Fall within Their Limit in Google Sheets. But that doesn’t feature the Isbetween with Filter use.

Here we can use Isbetween with Filter to filter “Test” wherever the “Actual” falls within the “Range From” and “Range To” values.

=filter(B3:B,isbetween(C3:C,D3:D,E3:E))
=filter(B3:B,C3:C>D3:D,C3:C<E3:E)

The output will be the tests “B” and “D”.

That’s all about how to use the Isbetween with Filter formula in Google Sheets.

Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.