I have 3 columns. column A is my "lookup value", with multiples of the same value, column B is the date performed, column C is the data I need. Based on the look up value, Column A, I need the most recent date, column B (max) to give me data in column c. the date will change daily as new data comes in
Thank you for your help
Try this...
Data Range
This array formula** entered in F1:
=INDEX(C1:C9,MATCH(1,(A1:A9=E1)*(B1:B9=MAX(IF(A1:A9=E1,B1:B9))),0))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
If the data is grouped by the lookup value and the dates are sorted in ascending order then it's a lot easier!
Last edited by Tony Valko; 06-01-2015 at 10:12 PM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
This thread helped me immensely!
I am doing something similar except the second lookup value is going to be in between certain values.
I was able to modify the posted formula replacing the = with <= and sorting the table so the second column goes from greatest to least. Works perfectly.
This method was much easier than some other methods I was reading about and trying to grasp.
Again, thanks for the help!
A non-arrray (just use Enter), using INDEx-MATCH-INDEX and AGGREGATE, assumes ABCD criterion is in E1
=INDEX($C$2:$C$10,MATCH(1,INDEX(($B$2:$B$10=AGGREGATE(14,4,$B$2:$B$10,1))*($A$2:$A$10=E1),0),0))
Last edited by Glenn Kennedy; 06-08-2015 at 04:04 PM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
You're welcome. Thanks for the feedback!
Thank you so much. I know just enough to be dangerous with formulas, the more I know the more dangerous I become.
I appreciate the help on my journey
Thank you!!! I appreciate the visual. That's the way my brain works, (or doesn't, as the case maybe)
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks