LinkBack URL
About LinkBacks
Hi
I am trying to find a formula that calculates the longest period from data in an adjacent cell. The attached spreadsheet might better explain this
Hi
I am trying to find a formula that calculates the longest period from data in an adjacent cell. The attached spreadsheet might better explain this
Last edited by Pickygame; 04-08-2009 at 01:16 PM.
In cell E1 enter this array formula:
=INDEX(B1:B100,MATCH(2,1/(B1:B100<>"")))
...and press CTRL-SHIFT-ENTER to confirm. The number 2 should appear. If you just press ENTER you will break the array and get a #DIV/0 error.
In E2, put this array formula:
=INDEX(A2:A100,MATCH(INDEX(C2:C100,MATCH(2,1/(C2:C100<>""))),C2:C100,0))
...also confirmed with CSE.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
The second array does the trick. I only put two columns in to assist because both questions should return the corresponding data in Column A. Thanks a lot
If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]
Thread marked as solved
I thought i had this solved but if any of the data in the adjacent column is the same excel gives the first value
Last edited by Pickygame; 05-01-2009 at 01:46 PM. Reason: not solved
Try this alternate array formula:
=INDEX(A:A,SUMPRODUCT(MAX((B3:B12=INDEX(B3:B12,MATCH(2,1/(B3:B12<>""))))*(ROW(B3:B12)))))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks