LinkBack URL
About LinkBacks
I have a list of dates and want to return a date which falls between two dates.
I have tried to use a VLOOKUP formula but can't seem to get it to work. Is there a way of doing this??
Thank you
I have a list of dates and want to return a date which falls between two dates.
I have tried to use a VLOOKUP formula but can't seem to get it to work. Is there a way of doing this??
Thank you
Can you clarify a little more pls?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
And perhaps post a small sample sheet?
Yes.
I have a list of training dates of one worksheet then I need to return one of those dates which falls between two dates in another sheet.
So i.e.
A1:A10 has a number of dates in it.
In B1 I have one date and in C1 another date
In D1 i want to find a date in the list A1:10 that falls between B1 and C1
Trying to upload something but computer at work being a bit on the slow side!
You are looking for JUST one date OR is possible to exist many dates as result?
Perhaps you can use the "Filter" function to find the result. Just my suggestion.
Fotis1991: I am looking to return just one date but there may be more than one date with in the range
nickh1981: Not heard of the Filter function will look into that
Thank you
And in this case which date do you want to return?Originally Posted by nappy1001
Can you pls post a small sample workbook?
Really sorry but still struggling to attach a file. Think its my work computer....
To answer the question though it really doesnt matter which date it returns as long as it falls between the two dates specified.
Is the list of dates in A1:A10 sorted?
Is the list of dates in A1:A10 date serial values or are they text strings that represent dates?
Since you have been unable to attach a sample file, these are questions that only you can answer.
If the list contains date serials and they are sorted in ascending order, you may be able to use the vlookup() (or other lookup function) to do this. Note that, when the fourth argument is true or omitted, vlookup will return the entry that is just smaller than lookup_value. So, if you put the larger/later of the two dates into a vlookup() function, it will return the value that is just smaller than the later date. Which, assuming that this value is also larger than the smaller date, should be a correct return value.
If the dates are input as text strings, this will not work, because the text string sort order will be different (alphabetical rather than date). In this case, I think I might work on getting the text strings converted to date values.
That is brilliant I think and I think this does solve my problem. To answer your question in the attachment I think i could write an If statement to say if the vlookup isn't between two dates then dont return a value if it does then return a vaule.
Thank you
Have tried a number of ways to sort this. Still can't attach any files and explorer crashes when I try!!
I have tried this formula where i have a list of dates in a2:a8 and two dates in c1,b1 for the date to be between but it doesnt work!!
if median(vlookup(C1,A2:A8,1)),C1,B1,vlookup(C1,A2:A8,1),no
It might be hard for us to help you debug this when it appears that the pasted version of the formula is missing parantheses. I mention that because one of the first things I look at with multiple nested functions like this is to make sure the parantheses are all matched up where they should be. Are you certain the parantheses for the median function are correctly placed? As written, it appears that it is taking the median of only one value, which would make the median function superfluous.
One suggestion: Use the "formula evaluation" tool (http://office.microsoft.com/en-us/ex...932.aspx?CTT=1 click on "evaluate a nested formula one step at a time"). This will allow you to follow the steps Excel is taking to evaluate the formula so you can see which steps are working the way you expect them to work, and which step is returning incorrect results.
Sounds fairly simple. I don't think you need a vlookup, add this formula to cell D1 and copy it down, it should give you what you are looking for:
=IF(AND(A1>$B$1,A1<$C$1),A1,0)
OR
=IF(AND(A1>=$B$1,A1<=$C$1),A1,0)
This assumes that column B is earlier date and C is the latter date of the date range.
Last edited by mcmuney; 10-04-2013 at 04:12 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks