Lookup not latest, but second latest value

[wonderfulsomebody123]

Currently I'm using the below formula successfully to bring up a date of a latest left digit in a sequence of two digits:

{=LOOKUP(2,1/("0"=LEFT(C:C,1)),A:A)}

What I want is the same above formula to instead bring up not the last date but the second last date. For example:

April 16, 2018	13
April 17, 2018	25
April 18, 2018	13

Right now, my formula above would bring up April 18, 2018. However, I want it to bring up April 16, 2018... not the last date, but the second last date with a particular left digit.

Please help! Thank you

 

[jasonb75]

This will do it. It would be best to use a fixed range, entire columns can be slow to calculate because of all the empty cells being processed.

=AGGREGATE(14,6,A:A/(LEFT(C:C,1)="0"),2)

 

[wonderfulsomebody123]

=AGGREGATE(14,6,A:A/(LEFT(C:C,1)="0"),2)

I tried your formula but all that shows is "#NAME?"

Would there be a way to use my same formula without much editing to get the result I want? Currently my formula works great, the result simply needs to look up the second last date rather than the last one. Thanks.

 

[jasonb75]

Which version of excel are you using' if you're getting that error then I'm guessing it must be a really old one?

There is no way of doing what you want with your formula, or any variation of it. Lookup functions can only return first or last match, not things in between. (At least not without making it far more complicated than it needs to be).