This post will guide you how to do a VLOOKUP between two dates and return corresponding value in Excel. How do I VLOOKUP between two dates and return corresponding value with lookup formula in Excel. How to lookup a value if the date fall between two dates in Excel.

1. VLOOKUP Value If Date Falls between Two Dates Using a Formula

Assuming that you have a list of data in Range A1:C4, which contain start date, end date, and Sales product, and you want to lookup a given date (2019/4/3), if this date falls between the start dates and end dates, and then return its corresponding product name. How to achieve it. You can use a formula based on the LOOKUP function and the DATE function to achieve the result of doing vlookup operation to lookup a date and return value.

Like this formula:

=LOOKUP(2,1/($A$2:$A$4<=DATE(2019,4,3))/($B$2:$B$4>=DATE(2019,4,3)),$C$2:$C$4)

Type this formula into a blank cell, such as: D1, and then press Enter key in your keyboard. And the value will be returned in Cell D1.

You just need to change the date number as you need in DATE function to do the VLOOKUP operation.

2. Return Values Between Two Dates Using a User-Defined Function (VBA)

You can also create a custom function with VBA code to replicate the behavior of the LOOKUP function for dates falling between two specific dates in Excel. Just do the following steps:

Step1: you can press ALT+F11 keys to open the Visual Basic for Application or navigating to the Developer tab and clicking on Visual Basic.

Step2:  click on Insert menu and then select Module to add a new module where you want to write the VBA code.

Step3: Copy and paste the below VBA code for the “LookupBetweenDates” function into the module window. Save and close the VBA project.

Function LookupBetweenDates(lookupDate As Date, startDateRange As Range, endDateRange As Range, resultRange As Range) As Variant
    Dim i As Long
    Dim lookupResult As Variant

    For i = 1 To startDateRange.Rows.Count
        If lookupDate >= startDateRange.Cells(i, 1).Value And lookupDate <= endDateRange.Cells(i, 1).Value Then
            lookupResult = resultRange.Cells(i, 1).Value
            Exit For
        End If
    Next i

    LookupBetweenDates = lookupResult
End Function

Step4: type the below custom function “LookupBetweenDates” to perform lookup based on the date criteria.

=LookupBetweenDates(E2, $A$2:$A$4, $B$2:$B$4, $C$2:$C$4)

Step5: The cell will now display the value corresponding to the date that falls between the two specified dates in the lookup range.

3. Video: VLOOKUP to Return Value if Date Falls Between Two Dates

This video tutorial, where we will demonstrate two efficient methods to utilize VLOOKUP in Excel for returning values when dates fall between two specific dates.

4. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.

5. Related Functions

• Excel LOOKUP function
  The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
• Excel DATE function
  The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…