Return 2nd most recent value

[csliger931]

Newbie here. I am looking for a function that will return the 2nd most recent value for a specified parameter. For example, lets say I am trying to calculate the Cycle Rate for certain Wells at a landfill. In order to do this, I need to take the most recent cycle count (Xn), subtract the 2nd most recent cycle count (Xn-1), and divide all of that by the elapsed time (Daten - Daten-1). The problem is that I have a massive amount of dates/values, and a lot of wells. So, if I want to calculate the cycle rate for Well "A", I don't know a formula for looking up the second most recent cycle count

(Xn-1) for A. I'm pretty sure I can use the MATCH INDEX function for returning the most recent cycle count for Well "A", but I'm not sure about 2nd most recent. Any tips/helps would be appreciated. I am not against doing a Macro, but my Macro skills are non-existent, so I would prefer a function/nested function if there is one. Thanks!!!​

 

[gaz_chops]

Welcome to the forum, it will help to get an answer if you provide a sample of your data and show what results you are after.
Use the XL2BB option to post the sample data.

 

[csliger931]

Unfortunately our work Administrator has blocked add-ins to Excel, but I have attached an image with the values that I want highlighted. I've also included a table below (the desired values that I want the function to retrieve are in the last column...I want it to pull the 2nd most recent Cycle Count for the specified well ( 3 for LW-A and 6 for LW-B) ). Thanks!

WELL	DATE	CYCLE COUNT		WELL	2ND MOST RECENT CYCLE COUNT
LW-A	7/16/20	3
LW-B	7/12/20	6		LW-A	3
LW-A	7/12/20	1		LW-B	6
LW-B	7/15/20	7
LW-C	7/17/20	9
LW-A	7/18/20	2

 

[gaz_chops]

Try
=INDEX($D$4:$D$9,MATCH(AGGREGATE(14,6,($C$4:$C$9)/($B$4:$B$9=$G4),2),$C$4:$C$9,0))

 

[gaz_chops]

Hmm it doesn't work if there are duplicate dates.......

 

[gaz_chops]

Ok, this appears to work

=INDEX($D$10:$D$17,MATCH($G7&AGGREGATE(14,6,($C$10:$C$17)/($B$10:$B$17=$G7),2),$B$10:$B$17&$C$10:$C$17,0))

Enter as an array using Ctrl, Shift & Enter

 

[gaz_chops]

This does the same but is a non array formula, pretty sure there is a better solution, but it's late

=AGGREGATE(14,6,($D$10:$D$17)/($B$10:$B$17=G7)/($C$10:$C$17=AGGREGATE(14,6,($C$10:$C$17)/($B$10:$B$17=$G7),2)),1)

Just so you know, the 2 in red is what tells it to return the 2nd Largest Value.

 

[csliger931]

Gaz you are the MAN!! That worked great! I wasn't even aware of an "Aggregate" function (I know, I'm a novice...), but I'm definitely going to have to look into it. Thanks again!

 

[gaz_chops]

You're welcome and thanks for the feedback.