Second to last value from table with condition

Occasional Contributor

Hi experts


Can you help me with the below?


I have a list of cash values in a table, example as follows by a rotating week number (1-4).


A ------------ B

1 ----------- 40

2------------- 30

3------------ 22

4 -----------56

1 -----------33

2 -----------15

3 -----------9







What I want to do is have a formula to always pick out the second to last value corresponding to the week number. For example if I wanted the second to last value of week 4....

The formula would spit out = 40


If I wanted the second to last value of week 2....

The formula would spit out = 15


I have a formula to get the last value for each week... but second to last I am struggling with. I also need to mention the formula must ignore blank values!!!


Any help appreciated



6 Replies




Is this what you are looking for? If you don't work with Office365 or 2021 you have to enter formula as arrayformula with ctrl+shift+enter.


Thanks so much for your reply. Sadly haven't got it to work like in your example.

I have knocked up an example spreadsheet. Hopefully this makes sense on how I am trying to use a formula for the second to last value in list?

I also have a formula for last value, this works fine as you will see.

Can you take a look and see what I mean from the cells highlighted in yellow?

Thank you again








If you don't have Office 2021 or Microsoft 365, confirm with Ctrl+Shift+Enter.

Thanks for these.... tried in the example spreadsheet and worked from copy & paste

Then added your formula to my actual spreadsheet by only adjusting the rows/columns to match and now get the #NUM! error

Is this potentially a formatting issue?


You'll have to adjust the formula for the actual range of course.

If that doesn't work for you, please attach a workbook that better represents your real setup.


Just in case, this one doesn't require CSE if you are not on Excel 365/2021

=INDEX( $C$6:$C$58, AGGREGATE(14, 6, 1/($A$6:$A$58=$F$5) / ($C$6:$C$58 <> "") *(ROW($A$6:$A$58) - ROW($A$5) ), $G$5 ) )

It's always better to mention Excel version.