I have 4 columns (Date (A), Open (B), High (C), Low (D)) in date order with the newest at the top. I have a value in G1, I'd like to see the oldest date returned in H1 of when a value in the (D) column is lower then G1. I've been looking at match and vlookup but what I can't seem to figure out is how to return the oldest date. Thank you for the help.
Could you please upload a sample workbook with required output
Like my answer, then click * below
Maybe:
=LOOKUP(2,1/($D$2:$D$10<$G$1),$A$2:$A$10)
Quang PT
date.xlsxOriginally Posted by satabp
Thank you.
Formula in my previous post to adapt your range:
In H2:
=LOOKUP(2,1/($D$2:$D$297<=$G2),$A$2:$A$297)
H3:
=LOOKUP(2,1/($C$2:$C$297>=$G3),$A$2:$A$297)
Awesome, thank you very much. Curious though, what in the formula is telling it to start looking from the bottom (oldest first) instead of the top (newest first)?
Nice to hear it work!
LOOKUP is a function that can looks for exactly ( or nearly) data in range 1 and returns corresponding data in range 2.
range 1 = 1/($D$2:$D$297<=$G2) return 1 where equals G2, else ERROR : {!DIV/0;!DIV/0;....;1;1;!DIV/0}
assuming range 2 = {date 1; date 2;....;date 99; date 100; date n}
=LOOKUP(2,1/($D$2:$D$297<=$G2),$A$2:$A$297)
look up number "2" (or others that greater than 1) in range 1, find the nearest value from the bottom ( that is last
number "1"), then returns coresponding date in range 2 (that is date 100)
Hope it helps much!
Cool, I do understand some, but what I don't get is how does it know to find the nearest value from the bottom and not the top?
This is how LOOKUP did: find the exact or approximate match to value. The lookup_range must be sorted in accending orderlook up number "2" (or others that greater than 1) in range 1, find the nearest value from the bottom ( that is last
number "1")
LOOKUP(5,{1,2,3,4,5,6},{"A","B","C","D","E","F"}) = "E" : find exactly
LOOKUP(5,{1,2,3,4,5,5},{"A","B","C","D","E","F"}) = "F" : find exactly to last match in range
LOOKUP(6,{1,2,3,4,5,5},{"A","B","C","D","E","F"}) = "F" : find last approximate match
LOOKUP(5,{1,1,1,1,1,1},{"A","B","C","D","E","F"}) = "F" : find last approximate match
LOOKUP(2,{1,1,1,1,1,1},{"A","B","C","D","E","F"}) = "F" : find last approximate match
LOOKUP(2,{1/0,1,1,1,1/0,1/0},{"A","B","C","D","E","F"}) = "D" : find last approximate match
Hope this helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks