How to VLOOKUP to Return Value if Date Falls between Two dates in Excel

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.

VLOOKUP Value If Date Falls between Two Dates


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.

vlook value if date falls two dates1

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

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

 

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)…

 

Related Posts
VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

VLOOKUP with Multiple Lookup Values

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP Data by Date

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Workbook
VLOOKUP - Retrieve Data from Another Workbook 1

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Worksheet
VLOOKUP - Retrieve Data from Another Worksheet 3

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match ...

Average the Last N Numeric Values in Excel
Average the Last N Numeric Values 7

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How to Find the First or Last Positive or Negative Number in a Column/List in Excel
How to Find the First or Last Positive or Negative Number in a Column9

Suppose we have a list of data entry, we want to look up the first positive number among them, is there any way to find it out? And if we want to look up the first or last negative number, ...

How to Find the Last or First Value in a Range Greater Than X in Excel
find last value greater thatn x2

This post will guide you how to find the first value greater than a specified value in a range of cells in Excel. How do I find the last value greater that a given value in a row with a ...

Sidebar