LinkBack URL
About LinkBacks
I have attached a very basic and simplistic spread-sheet with very clear instructions on my issue.
Please spare 2 minutes to have a look (for those who understand excel better than I do should find this a breeze).
Thanks in advance!
I have attached a very basic and simplistic spread-sheet with very clear instructions on my issue.
Please spare 2 minutes to have a look (for those who understand excel better than I do should find this a breeze).
Thanks in advance!
=index(a2:b41,match(a12,a2:a41,1),2)
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
I believe this is what you are looking for.. Looking at your data and instructions it appears to me that A12 is actually the 8th oldest not the 9th oldest as you have represented. this formula seemed to work in A12
Formula:
Please Login or Register to view this content.
If the data is sorted into date order, you will see that there are several consecutive dates. What is special about those 2 dates and not the others?
Partial data.JPG
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hey everyone.
So the date AND time needs to be used (sorry i should have made that clearer). They CANNOT be simply sorted in chronological order.
That still leaves the question: What is so special about the two dates that you have highlighted in your data that doesn't apply to the other data?
I sorted in order to show you that there were many matches to your description of the problem - not that sorting was the answer to your problem.
look at post #3
Hi Dosydos, I have tried this but it doesn't appear to have worked. I need a formula that looks "up" from the row in question and not the entire data set columns e.g. no A:A, but from A$2$:A12.Originally Posted by dosydos
Hi Dosydos, I have tried this but it doesn't appear to have worked. I need a formula that looks "up" from the row in question and not the entire data set columns e.g. no A:A, but from A$2$:A12.
ok so just lock cell A2 and put this formula in C12
=INDEX(B:B,RANK.AVG(A12,$A$2:A12,1)+1)
Hmm... just thinking, this wouldn't work if (as newdoverman has mentioned) if there are other dates and time duplicates.
If there is an entry above the row in question with the same date and time, I want to know ignore duplicates (any) and find the row with the date that chronologically makes sense before (and then return the value).
How would I do this?
Ah I see what you're saying. So from the row in question, when I look "up" column A, I want to look up from where I am now and all the data upwards to find the date and time that my entry would theoretically chronologically "stack-up". Another way of saying this is; looking "up" column A, where does my date belong "closes" too relative to the other dates. If there are duplicate dates and time entries, then I want to find the HIGHEST row number, and in turn, the subsequent value I am after that corresponds to this row...
Please take a minute to watch this explanation video I have put together: http://screencast.com/t/YWfm113L1 ( i have adjusted the original spreadsheet for this example video)
Let me know if this makes sense?
=VLOOKUP(A12,$A$2:B11,2,TRUE)
This works until I get a duplicate date and time. In the case where I get a duplicate date and time in (example) row 10, 6 and 3, I just want the corresponding value in column B that is the lowest row number possible... So in this case B3...
How can I do this?
can someone please help me???
True has a numeric value of 1 within the VLOOKUP and other functions False has a value of 0. The numbers 1 and 0 mean exactly the same as True and False.
Unless the list is ordered in ascending order, TRUE will not return with any reliability the value that you are looking for. Only FALSE will find an exact match and faithfully return the correct value for that exact match.
MATCH will accept -1, 0 and 1 as match types.
1 returns the largest value that is less than or equal to the lookup value if values in ASCENDING order.
0 returns the exact match to the lookup value regardless of order.
-1 returns the smallest value that is greater than or equal to the lookup value if values in DESCENDING order.
This ARRAY formula (enter with Ctrl + Shift + Enter) will find the first date and return the value that goes with it
Formula: [Select Code] copy to clipboard
Formula:Please Login or Register to view this content.
This ARRAY formula will return the value associated with the first date greater than Jan 4 2016
Formula: [Select Code] copy to clipboard
Formula:Please Login or Register to view this content.
If you are wanting to find the value in column B associated with a date in column A that occurs 3 times, create a helper column C with this formula filled down. This will enter a 3 for every date that occurs 3 times in the range
Formula: [Select Code] copy to clipboard
Formula:Please Login or Register to view this content.
Then use this formula to retrieve the value that corresponds to the first occurrence of the triple date
Formula: [Select Code] copy to clipboard
Formula:Please Login or Register to view this content.
The problem that you are trying to solve is quite awkward to return the values that you want without returning the values that you don't want.
This is a copy of what I sent to you last night....don't know where it went if you didn't get it.
To identify duplicates you can try this...modify to fit the data
Formula:Please Login or Register to view this content.
Then you can use to retrieve the value for Duplicate.
Formula:Please Login or Register to view this content.
Thank you newdoverman for taking the time to help.
What you are saying is completely logical and answers one aspect of the formula I need.
The trickiest part is that I cannot rearrange column "A" into ascending or descending order. So now imagine that the date I am looking up in column A, there are no duplicates. However, this time I need to find the date that makes most chronological sense (i.e. what would be the closest date that would have logically come just before my date)?
This I am baffled with?
Thank you newdoverman for taking the time to help.
What you are saying is completely logical and answers one aspect of the formula I need.
The trickiest part is that I cannot rearrange column "A" into ascending or descending order. So now imagine that the date I am looking up in column A, there are no duplicates. However, this time I need to find the date that makes most chronological sense (i.e. what would be the closest date that would have logically come just before my date)?
This I am baffled with?
Maybe this will give you an idea. I inserted 2 helper columns. The first strips out the times and the second strips out duplicates leaving blanks where the duplicates would be but enters the date in the last occurrence of the date.
Helper 1 D2 enter and fill down
Formula:Please Login or Register to view this content.
Helper 2 E2 enter and fill down
Formula:Please Login or Register to view this content.
Possible solution F2 and fill down
Formula:Please Login or Register to view this content.
This is about all that I can think of.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks