Log In
Register
Home
Active Member
May 24, 2024
Offline
I've been trying to use VLOOKUP to find a value between two dates and return the corresponding value from a different column. I have a dataset where the first column contains the start dates, the second column contains the end dates, and the third column has the values I want to retrieve.
Here's what I'm trying to achieve:
I have a specific date, let's say 15th June 2024.
I want to look up this date in my dataset and find which date range it falls into.
Then, I want to return the value from the third column that corresponds to this date range.
I came across a formula that seems to be on the right track, but I'm not sure how to adjust it to my needs:
$$
=LOOKUP(2,1/(\text{StartDates}<=\text{LookupDate})/(\text{EndDates}>=\text{LookupDate}),\text{Values})
$$
Could anyone please guide me on how to modify this formula for my dataset, or is there a better way to approach this problem?
Trusted Members
October 17, 2018
Offline
Hi Jorge,
First I suggest you include the Excel version you're using.
Secondly a sample file will tell us more than words
Trusted Members
Moderators
November 1, 2018
Offline
The formula is basically:
=LOOKUP(2,1/(Column1<=LookupDate)/(column2>=LookupDate),column3)
where LookupDate is your lookup date, column 1 is the start dates range, column 2 is the end dates range and column 3 is the values column.
Active Member
May 24, 2024
Offline
The formula is basically:
=LOOKUP(2,1/(Column1<=LookupDate)/(column2>=LookupDate),column3)
where LookupDate is your lookup date, column 1 is the start dates range, column 2 is the end dates range and column 3 is the values column.
Thanks for your suggestion.
1 Guest(s)
