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?
Hi Jorge,
First I suggest you include the Excel version you're using.
Secondly a sample file will tell us more than words
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.
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.
