Lookup a Date Between Two Dates in Google Sheets

The lookup functions are designed to search for a key in a single column or row, not in two columns or rows. However, when it comes to searching for a date between two dates, it requires two search key columns. Let’s explore how to accomplish this in Google Sheets.

Sample Data

The sample data consists of the Start Date, End Date, and Product in columns A to C.

We want to look up a date between the start and end dates in each row of the range and return the product name.

Sample data for looking up a date between two dates in Google Sheets

In Google Sheets, we will see how to use VLOOKUP and XLOOKUP functions to lookup a date between two dates (start and end dates) in each row.

VLOOKUP a Date in Start and End Date Columns

We understand that VLOOKUP can’t search in two columns simultaneously. Therefore, we must merge the start and end date columns into a single column using the following logic.

We want to search for the date 04/01/2018, entered in cell F2, within the start and end dates in columns A2:B. Here are the step-by-step instructions:

The following formula will merge columns A2:A and B2:B into a single column containing 1s and 0s, where 1 represents a match with the search key, and 0 for no match.

=ArrayFormula((A2:A<=F2)*(B2:B>=F2))

Note: The Explanation has been added after a few paragraphs below.

Combined start and end date column for lookup

This merged column will serve as the VLOOKUP search column, which will be the first column in the lookup range.

We want to return values from column C. Therefore, we combine C2:C with this search column using HSTACK as follows.

=HSTACK(ArrayFormula((A2:A<=F2)*(B2:B>=F2)), C2:C)
Combining merged date columns and result column for lookup range

This combined array will be used as the range parameter in the VLOOKUP.

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

Since this range contains 1s or 0s in the first column, where 1 represents a match, we set the search_key as 1. The index column will be 2 because the product column is the second column in this range.

So the formula will be:

=VLOOKUP(1, HSTACK(ArrayFormula((A2:A<=F2)*(B2:B>=F2)), C2:C), 2, FALSE)

Explanation:

=ArrayFormula((A2:A<=F2)*(B2:B>=F2))

This array formula evaluates each cell in the range A2:A to check if it’s less than or equal to the date in cell F2 (the lookup date). Similarly, it checks each cell in the range B2:B to see if it’s greater than or equal to the lookup date.

The multiplication operation (*) combines the results of these comparisons, producing a resulting array where each element is either 1 (if the condition is true) or 0 (if the condition is false).

Therefore, the resulting array effectively indicates whether the lookup date falls within the range defined by the start and end dates in columns A and B respectively.

XLOOKUP a Date in Start and End Date Columns

Another method to search for a date between two dates is using XLOOKUP.

The formula follows the same logic as described above. The only difference is that we should specify the search and result columns separately in the formula. There is no need to combine them using HSTACK as previously done. The search key will be 1, and the index column is not required.

Syntax: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Formula:

=XLOOKUP(1, ArrayFormula((A2:A<=F2)*(B2:B>=F2)), C2:C)

Where:

  • search_key: 1
  • lookup_range: ArrayFormula((A2:A<=F2)*(B2:B>=F2))
  • result_range: C2:C

Using Multiple Search Keys in Start and End Date Columns

In the above example, we have a single search date in cell F2.

But what if you want to look up the dates in the range F2:F3 within columns A2:A and B2:B, and return the corresponding products from column C2:C?

Example of looking up dates between start and end dates

Simply including F2:F3 directly in the formulas won’t provide the desired result. Instead, you may utilize the MAP lambda function.

Syntax: MAP(array1, [array2, …], lambda)

In this case, we’ll use F2:F3 as array1. There is no need for array2. The lambda function will be the VLOOKUP or XLOOKUP formula mentioned previously.

VLOOKUP Multiple Dates in Start and End Date Columns

=MAP(F2:F3, LAMBDA(value, VLOOKUP(1, HSTACK(ArrayFormula((A2:A<=value)*(B2:B>=value)), C2:C), 2, FALSE)))

XLOOKUP Multiple Dates in Start and End Date Columns

=MAP(F2:F3, LAMBDA(value, XLOOKUP(1, ArrayFormula((A2:A<=value)*(B2:B>=value)), C2:C)))

It’s important to note that using MAP with a large dataset may lead to performance issues.

Resources

Here are some related resources regarding looking up dates within a range in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

29 COMMENTS

    • Hi, R. Krishna,

      We can use the latest function bundle (Lambda and helper functions) to create a much cleaner formula.

      You may not require the HLOOKUP.

      Please explain the problem in detail.

  1. Hi, I have a sheet with the following.

    A1 – Customer name
    B1 – Date of first contact
    C1 – Date of agreement
    S1:BB1 – The month of the year (it runs 36 months across)

    When we meet a customer, we enter A1 and B1. When they sign, that is C1.

    Then when we bill them, we place a dollar value in the appropriate cell for that month’s revenue (S2:BB2).

    The goal is to calculate how many days it was between the “Date of first contact” (column B) and the date of the first payment (Columns S:BB).

    • Hi, Stephen Bessette,

      In cell D2 enter the below formula to get the month (S1:BB1) correspond to the first non-blank value in that row.

      =index($S$1:$BB$1,MATCH(FALSE,ISBLANK(S2:BB2),0))

      Drag this formula down the column.

      Now you may be able to find the number of days in another column. For that, empty E2:E and enter the below formula in cell E2.

      =ArrayFormula(if(len(B2:B),days(D2:D,B2:B),))

      This formula will expand down.

      If you want, you can replace the above first formula with an array formula.

      Empty D2:D and enter the following formula in cell D2.

      =index(sortn(query(split(flatten(ROW(S2:BB100)&"|"&S1:BB1&"|"&S2:BB100),"|"),
      "Select * where Col3 is not null"),9^9,2,1,1),0,2)

      This formula has a draw-back. You must have a dollar value in each row.

      I’ll try to explain the above formula in my upcoming tutorial.

  2. Hi,

    This article is so inspirational – thank you!

    I want to pull such data from a start-end dates range in one sheet into a calendar-shaped sheet in the same workbook (there is one cell for each day to count all the instances in which we have bookings for that day but I cannot fix the formula yet). I need a simple formula to insert for each day of the month in the calendar sheet to calculate the number of bookings from the range from-to on the first sheet – is this possible?

    Kind regards

    • Hi, Eva,

      I can try if you could share a mockup/sample of your sheet. Use the “Reply” below to share the URL (I’ll keep that comment private/[won’t publish]).

  3. Hello, I have tried the following but have gotten the error of “Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1095. Actual: 1093”.

    Could you help with this formula? Does it work for multiple years?

    =ArrayFormula(IF(LEN(E2:E),(VLOOKUP(E2:E,{row(indirect("A"&A2):indirect("A"&B13)),transpose(split(join("",(rept(C2:C13&"|",(B2:B13-A2:A13)+1))),"|"))},2,FALSE)),))

    Start Date | End Date | Quarter
    8/1/19 | 10/31/19 | Q1 2020
    11/1/19 | 1/31/20 | Q2 2020
    2/1/20 | 4/30/20 | Q3 2020
    5/1/20 | 7/30/20 | Q4 2020
    8/1/20 | 10/31/20 | Q1 2021
    11/1/20 | 1/31/21 | Q2 2021
    2/1/21 | 4/30/21 | Q3 2021
    5/1/21 | 7/30/21 | Q4 2021
    8/1/21 | 10/31/21 | Q1 2022
    11/1/21 | 1/31/22 | Q2 2022
    2/1/22 | 4/30/22 | Q3 2022
    5/1/22 | 7/30/22 | Q4 2022

    • Hi, CC,

      It’s because of the three “End Dates” 07/30/20, 07/30/21, and 07/30/22. It should be 07/31/20, 07/31/21, and 07/31/22. If you change that, the formula will work.

      But please note that there is one more solution. In the last paragraph, I have linked to another tutorial that has a much simpler solution.

      =ArrayFormula(IFERROR(VLOOKUP(E2:E,A2:C,3,1)))

      The above Vlookup only requires sequential dates. The above date issue won’t affect it.

  4. Hi Prashanth,

    I’m still pretty new to Google Sheets, but I’m enjoying myself.

    I’m afraid I don’t understand the second thing about your master formula.

    You have an IF formula where the condition is LEN(, but if I understand LEN correctly, this will just generate a number, something like 42 for example.

    How can an IF formula work when a condition is just a number? That is, what is IF doing when I write =IF(42,"True","False")?

    I hope my question is clear.

    • Hi, Anthony,

      I understand the purpose of LEN is clear to you. It is just to return the number of characters in a cell.

      But when we use it with IF, the purpose is different.

      Example.

      =if(len(A1),"Fruits","Vegetables")

      It means if A1 has any value (or A1 has length), return “Fruits”, else “Vegetables”.

      It’s an alternative of =if(isblank(A1),"Vegetables","Fruits") or =if(not(isblank(A1)),"Fruits","Vegetables")

  5. First, I’m in a bit over my head, but your formula is the best explanation I’ve read, but I’m still struggling.

    In the sheet below (link removed by admin), I need a formula for tab 1, Column E that will return the webinar date (tab 2, Column A) based on the submitted date (tab 1, Column A).

    Basically, if the submitted date falls between the dates in tab 2 column C & D, then return the corresponding date listed in tab 2 Column A.

    Does that make sense?

    • Hi, Adam Housley,

      You can try the below Vlookup Date Range formula.

      =ArrayFormula(IFNA(vlookup(A2:A,sort({'Info Zoom Formulas'!C2:D,'Info Zoom Formulas'!A2:A}),3,1)))

      This formula to be keyed in E2. It will expand to the rows down. Also covered the entire rows in both the sheets.

      Note: Formula added to your sheet. If you use this formula in a new sheet, you may format the result to date from Format > Number > Date. Otherwise, the result will be the date values.

  6. Hi Prashant, awesome article! I would like to implement the same solution but adding a condition. Is it possible? To clarify my question, here’s a concrete example: A company is offering rewards based on city and date, e.g

    Paris | 2-Aug | 5-Aug | $2
    Paris | 6-Aug | 8-Aug | $4
    London | 2-Aug | 5-Aug | $3
    London | 6-Aug | 8-Aug | $5

    I would like an updated formula to give the reward in Paris and London on 3-Aug.

    With my best effort using your tutorial but it doesn’t work for London because the date range doesn’t repeat

  7. Hi Prashanth,

    I read your article with interest; I found very useful. It does part of what I need.

    I have a list of Start/End date range with an absence type for each range:

    Start Date End Date Absence Type
    27/01/2020 31/01/2020 Annual Leave
    03/02/2020 05/02/2020 Sick Leave
    02/03/2020 05/03/2020 Unpaid Leave

    I have used this formula =TRANSPOSE(ARRAYFORMULA(TO_DATE(ROW(INDIRECT("A"&A2):INDIRECT("B"&B2))))) for Row (2) thru Row (4), obviously with corresponding column values. It generates the following rows starting from E2:

    27/01/2020 28/01/2020 29/01/2020 30/01/2020 31/01/2020
    03/02/2020 04/02/2020 05/02/2020
    02/03/2020 03/03/2020 04/03/2020 05/03/2020

    I would like to have the following as the final result:

    27/01/2020 Annual Leave
    28/01/2020 Annual Leave
    29/01/2020 Annual Leave
    30/01/2020 Annual Leave
    31/01/2020 Annual Leave
    03/02/2020 Sick Leave
    04/02/2020 Sick Leave
    05/02/2020 Sick Leave
    02/03/2020 Unpaid Leave
    03/03/2020 Unpaid Leave
    04/03/2020 Unpaid Leave
    05/03/2020 Unpaid Leave

    Please note that the number of start/end ranges are dynamic; ie, B5 is dynamic and can higher number like B100.

    Is there any dynamic way, using formulas, to achieve the above?

    Many thanks for your help in advance.

  8. Hey Prashanth! I get an error when I try to recreate your sample using the mater formula. It says “Error
    VLOOKUP evaluates to an out of bounds range.” in the G cells when I put a date in F.

    • Hi, Gregory,

      I have earlier detailed in a tutorial on how to list all the Sundays between two dates.

      Google Sheets: List All Sundays from a Start and End Date.

      I have slightly changed the formula given in that tutorial to suits your requirement.

      Enter the start date in cell A1 and the end date in cell A2. Then use this formula in cell B1.

      =query(ArrayFormula(TO_DATE(row(indirect("E"&A1):indirect("E"&A2)))),"Select Col1 where dayOfWeek(Col1)<>1 and dayOfWeek(Col1)<>7")

      Best,

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.