How to vlookup between two dates and return corresponding value in Excel?
Supposing you have a report of some projects schedule, now, you want to get the specific project of a given time which is between two given dates of the schedule. Take the following screenshot data for example, I would like to look up the given date 8/13/2015, if this date falls between the start dates and end dates within the report, then return its corresponding project name. This article, I will introduce you the LOOKUP function to deal with this problem.
Vlookup for a value in a list:
With this formula of Kutools for Excel, you can quickly vlookup the exact value from a list without any formulas.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
To get the relative value which date falls between two dates, the below Lookup formula may help you. Please do as this:
Enter this formula into a blank cell where you want to place the result: =LOOKUP(2,1/($A$2:$A$7<=F1)/($B$2:$B$7>=F1),$C$2:$C$7), (A2:A7 is the range of start dates and B2:B7 is the end date range in your report, the F1 is the given date which you want to get its corresponding value.), and then press Enter key to get the result you need. See screenshot:
Note: If the date doesn’t exist, you will get an error value after typing this formula.
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 days agoHi. What is the logical explanation to use this formula: =LOOKUP(2,1/($A$2:$A$7<=F1)/($B$2:$B$7>=F1),$C$2:$C$7)? How does it work? Thanks.
To post as a guest, your comment is unpublished.· 19 days agoa godsend - exactly what I was looking for! much thanks
To post as a guest, your comment is unpublished.· 2 months agohello, what if in the columns, there are data aside from dates. How can I get the date from the range?
E.g. Column A - 03/19/2019, Column B - Approved, Column C - Pending, Column D - ---. In this case, how can I get 03/19/2019?
To post as a guest, your comment is unpublished.· 11 months agoi have two sheets, Sheet No 1 has Selling date, Grade & Item Code, Sheet no 2 has Price applicable from date, to date, Item code & Garde. please help to solve using vlookup, index or any other function in excel ?
To post as a guest, your comment is unpublished.· 11 months agoHello, what if I want to add another criteria. For example, if there was another column D called "Group ID" that I want to add in the formula. i.e. If group ID=302511 then LOOKUP(2,1/($A$2:$A$7<=F1)/($B$2:$B$7>=F1),$C$2:$C$7). Is this possible?