Download the workbook 👉 HERE and follow along.

Learn how to lookup values between dates using the FILTER function with Boolean logic. The goal is to find records with dates between the start date in G4 and the end date in H4.

lookup values between dates

We’ll keep the data on a separate, possibly hidden sheet. Only the date selection and results list will be visible to users. This keeps your interface clean and user-friendly.

Build Dynamic Drop Down Lists in Excel

Here is a step-by-step guide to creating dynamic “From” and “To” Dropdown Lists for the date selection.

What Is a Dynamic Drop Down List?

A dynamic dropdown list updates automatically based on data source changes. It also updates based on user input, ensuring users see current options. This ensures you always get up to date options to select from without manual updates.

lookup values between dates with drop down lists

Step 1: Prepare Data Lists

  • Create data preparation lists to manipulate dates before displaying them in the dropdowns.
  • Note: Dates might not look formatted correctly initially.

Step 2: Format the Date Cells

  • Select cells M2:N54.
  • Apply the Short Date format. (This allows for future date additions.)

Step 3: Get Unique and Sorted Date List

  • Use the UNIQUE and SORT Functions to extract and sort unique dates.
  • Enter this formula in cell M2:
=SORT(UNIQUE(A5:A53))
  • UNIQUE(A5:A53): Extracts unique dates from the range
  • SORT: Sorts these dates in ascending order.

Click here to learn more about the UNIQUE and SORT functions.

Step 4: Set Up “From Date” Drop down List

  • Select cell G4.
  • Go to the Data tab -> Data Tools group -> Data Validation.
  • In the Data Validation dialog, under the Settings tab, choose List from the Allow dropdown.
  • In the Source field, enter:
=$M$2#

Explanation of the # Sign:

The # symbol indicates the “spill area” which is the range of cells directly below M2 that contain the sorted unique dates. As the list of dates grows or shrinks, the spill area automatically adjusts, ensuring the dropdown list is always up-to-date.

Check out this article when to use the Hash Sign (#) in Excel.

Add an Input Message:

  • Provide a simple message to guide users on how to use the form.

Step 5: Set Up “To Date” Drop down List

In this step, you’ll create a dynamic “to date” dropdown list in Excel. We’ll ensure the “to date” list includes only dates after the selected “from” date.

Generate the List with the Excel FILTER Function

In cell N2 enter this formula:

=FILTER(M2#, M2#>G4, "")
  • M2#: This is the range of cells containing the data you want to filter. Here, M2# represents the dynamic range starting at cell M2 and including all the unique, sorted dates from the “from” list.
  • M2#>G4: This defines the condition that each date must meet to be included in the result. In this case, M2#>G4 checks each date in the array to see if it is greater than the selected “from” date in cell G4. If a date meets this condition, it is included in the filtered list.
  • “”: This specifies what to display if no dates meet the condition. Here, “” is used to display an empty cell if there are no dates after the selected “from” date.

This filters the dates in column M. It shows only dates after the “from” date in G4. It keeps the original sorting.

Set Up the Dropdown:

  • Select cell H4.
  • Go to Data -> Data Tools -> Data Validation.
  • In the Data Validation dialog, under Settings, choose List from Allow.
  • In the Source field enter:
=$N$2#

Explanation of the # Sign: The # symbol denotes the dynamic spill area, adjusting as the list updates.

The dropdown list in H4 now shows dates after the selected “from” date. It keeps your data accurate and up-to-date.

Lookup Values Between Dates

The next step is to lookup values from the table in columns A through C. We want to get values where the sale date is equal to or between the two selected dates.

Set Up Your Dates:

  • Enter your start date in cell G4: 1/23/2019
  • Enter your end date in cell H4: 1/28/2019

Use the FILTER Function to Lookup Values Between Dates

  • Select cell G7.
  • Enter the formula:
=FILTER(A5:C53, (A5:A53>=G4)*(A5:A53<=H4), "No records found")

This formula filters records from the table in columns A through C where the sale date is between the dates in G4 and H4.

đź’ˇ The list updates automatically when the dates are changed.

Format Your Data:

  • Apply the Short Date style to the dates in column G.
  • Apply the Comma style (zero decimal places) to the sale amounts in column I.
lookup values between dates with filter function

Explaining FILTER Function with Boolean Logic

Understanding the Logic

The formula checks if dates in column A are between the “from” date in G4 and the “to” date in H4.

(A5:A53>=G4)*(A5:A53<=H4)
  • A5:A53>=G4: Checks if each date is on or after the start date.
  • A5:A53<=H4: Checks if each date is on or before the end date.

The “*” acts like the word “AND”, ensuring both conditions are met.

Testing the Conditions

  • Highlight the first test in the formula and press F9 to see TRUE or FALSE for each date.

đź’ˇ Press Ctrl + Z or click Undo to restore the formula to its original state after testing

Repeat for the second test to see the evaluations against the end date.

Where a date is less than or equal to the selected date, the evaluation produces a TRUE response; all other responses are FALSE.

Boolean True/False Interpretation

  • Excel interprets TRUE as 1 and FALSE as 0.
  • Only records with dates that return TRUE for both conditions are included in the filter.

With this method, you can easily filter data within a specific date range.

How to Make Your Source Data Dynamic in Excel

Making your data dynamic helps ensure any new records are automatically included in your analysis. Follow these steps:

Convert Your Data to an Excel Table:

  • Select your data range in columns A through C.
  • Press Ctrl + T.
  • Confirm by clicking OK in the Create Table dialog.

Simplify Table Appearance:

  • Click the Design tab.
  • Click the Table Styles drop-down.
  • Choose Clear to remove any formatting.

Removing unnecessary colors helps your Excel run more efficiently, especially if the table is hidden. This keeps your data management streamlined and effective.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master to lookup values between dates in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.