Conditionally Lookup Dates in Date Range in Google Sheets (Array Formula)

GiGiKing·April 3, 2024·0 comments

A couple of months back, in August 2020, I received queries from two of my readers about how to conditionally lookup dates in a date range in Google Sheets. They needed an array formula to perform this task as there were multiple dates to consider.

Today, I want to share a powerful solution to this problem using Google Sheets. This formula will allow you to conditionally lookup dates in a date range effortlessly. So, let’s dive right in!

The Scenario

Imagine you have a dataset of travel dates and cities, and you want to pay or reward based on these criteria. The data table looks something like this:

Conditionally Lookup Dates in Date Range - Example

To tackle this scenario, we will use an array formula inserted in cell I3. This formula matches the city names given in the criteria column G3:G with the city data in column B3:B. It also compares the travel dates provided in the criteria column H3:H with the start and end date data in columns C3:D. Finally, it returns the corresponding value from column E.

The Array Formula

The array formula to conditionally lookup dates in a date range is as follows:

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

Before you try this formula in your own spreadsheet, here are three important things you should know:

  1. The formula doesn’t support using duplicate cities in the criteria range (G3:H).
  2. The formula is based on my Locale settings, so you might need to adjust the commas to semicolons in case of any errors. Refer to this guide for help.
  3. Follow the step-by-step instructions below to understand and customize the formula according to your needs.

The Logic

Let’s break down the logic behind the formula.

  1. filtered_data: First, we use the FILTER function to filter the data in B3:E based on the given criteria in G3:H. This creates a table of filtered data.

  2. vlookup(G3:G,filtered_data,4,0): Next, we use this filtered data as the range in a VLOOKUP function to assign the corresponding amounts to cities in G3:G.

To facilitate the filtering process, we use the ISBETWEEN function, which checks if the dates fall within the date range. This helps us conditionally lookup dates in the date range.

How the Formula Works

Here are the step-by-step instructions to understand the formula and customize it according to your needs:

  1. Use the following formula in cell F3: 
    =ArrayFormula(IFNA(vlookup(B3:B,G3:H,2,0)))

This formula matches the city names in B3:B and returns the corresponding dates from the criteria date column H.

  1. Use the following formula to test if the dates in F3:F fall within the date range given in the start date and end date columns (C3:C and D3:D): 
    =ArrayFormula(isbetween(IFNA(vlookup(B3:B,G3:H,2,0)),C3:C,D3:D))

This formula returns an array of TRUE/FALSE values, indicating whether each date falls within the date range or not.

  1. Finally, use the following formula to filter the data based on the TRUE values: 
    =FILTER(B3:E,isbetween(IFNA(vlookup(B3:B,G3:H,2,0)),C3:C,D3:D))

This formula will filter the original data table, leaving only the rows that match the condition.

That’s it! You have successfully learned how to conditionally lookup dates in a date range using an array formula in Google Sheets. Feel free to customize the formula and explore its possibilities.

If you have any questions or need further assistance, don’t hesitate to visit Crawlan.com. Happy sheeting!

Sample_Sheet_10621

GiGiKing

Related posts