A couple of months back, in August 2020, I got queries from two of my readers about how to conditionally Lookup dates in a date range in Google Sheets.

Their requirement was an array formula to perform the same as there are multiple dates to conditionally Lookup in a date range (start and end date columns).

That time, I offered them a drag-down formula.

But when I was writing a tutorial regarding the recently introduced ISBETWEEN function, a new member in the Google Sheets function family, I got the idea to materialize their requirements. At least, I think so.

As a side note, here is the link to the tutorial I have mentioned above – How to Use Isbetween with Filter Formula in Google Sheets.

Scenario:-

Please take a look at the below data in B1:E.

I want to pay/reward based on the travel date and city (“criteria to Lookup” as marked on the image).

Since there are multiple cities and travel dates (the data is growing, so there will be more cities in the future), I want a Lookup array formula to perform the same.

In the above example, I have an array formula inserted in cell I3 to conditionally Lookup dates in the date range.

The formula matches the city names given in the criteria column G3:G in the data column B3:B and dates provided in the criteria column H3:H in the start and end date data columns in C3:D.

Then it returns the corresponding value from column E. You can find that formula, its logic, and explanations below.

Array Formula to Conditionally Lookup Dates in Date Range in Google Sheets

Array Formula to Use in Cell I3:

=ArrayFormula(
     ifna(
        vlookup(
           G3:G,
           filter(
              B3:E,
              isbetween(IFNA(vlookup(B3:B,G3:H,2,0)),C3:C,D3:D)
           ),4,0
        )
     )
)

Before trying this formula to conditionally lookup dates in a date range in your Spreadsheet, you must know three important things. Here are them.

1. The formula won’t support using duplicate cities in the criteria range G3:H.
2. The formula is as per my Locale (Google Sheets > File > Spreadsheet settings). In case it returns an error, you must first check those settings. Ideally, changing commas to semicolons would solve such issues. This may help – How to Change a Non-Regional Google Sheets Formula.
3. Please follow the step-by-step instructions under the formula explanation section below to understand it. The motto is to learn the formula to edit it.

Logic in Concise

The idea is like this.

At present, there are no built-in functions for conditionally Match/Lookup dates in a date range (or start and end date columns) in Google Sheets.

So I have written an array formula using a combination of functions as above.

The logic of that formula is as follows.

It’s two-folded. I will explain it.

Here is the syntax of the above formula in I3.

=ArrayFormula(ifna(vlookup(G3:G,filtered_data,4,0)))

1. filtered_data

First, we will use the FILTER function to filter the data in B3:E based on the given criteria in G3:H.

As per the above criteria (please see the image above), the output will be the below two rows.

2. vlookup(G3:G,filtered_data,4,0)

Then we will use that (the above table [filtered_data]) as the range in one VLOOKUP to assign the amount (the last column in the table above) to cities in G3:G.

But the filtering won’t be easy as you think because there may be criteria in several rows in G3:H.

To facilitate the filtering, the core to conditionally Lookup dates in a date range, I have used the ISBETWEEN, which you can learn below.

How the Formula Conditionally Lookup a Date in Date Range

Here are the steps to get the filtered_data to use in Vlookup.

Syntax of the filtered_data: =filter(B3:E,date_is_Isbetween)

date_is_Isbetween

In cell F3, insert the below Vlookup array formula (We don’t require any helper column. This is just for explanation purpose).

=ArrayFormula(IFNA(vlookup(B3:B,G3:H,2,0)))

The Vlookup matches the city names (criteria) in B3:B and returns the corresponding dates from the criteria date column H.

To make things more simple, I have highlighted the returned values in column F.

We will use the result in an ISBETWEEN formula to test whether these dates (F3:F) fall in the date range given in the start date and end date columns.

=ArrayFormula(isbetween(IFNA(vlookup(B3:B,G3:H,2,0)),C3:C,D3:D))

Related: Find Whether Test Results Fall within Their Limit in Google Sheets.

We just want the rows that contain TRUE. This filter will do that.

=FILTER(B3:E,isbetween(IFNA(vlookup(B3:B,G3:H,2,0)),C3:C,D3:D))

We have done most of the part to conditionally Lookup a date in the date range in Google Sheets. Since we have identified the dates, what is left is to identify the cities.

Finally use the above filtered_data in Vlookup as mentioned. That is our final formula.

That’s all.

Thanks for the stay. Enjoy!

Sample_Sheet_10621