Second to last value from table with condition

[matt0020190]

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

4------------40

1------------18

2------------79

3------------45

4------------61

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

Matt

[#2]

@matt0020190 

=INDEX($B$1:$B$12,LARGE(IF(($A$1:$A$12=$D$1)*($B$1:$B$12<>""),ROW($1:$12)),2))

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.

[#3]

@Quadruple_Pawn 

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

[#4]

@matt0020190 

Use

=INDEX($C$6:$C$58,LARGE(IF(($A$6:$A$58=F5)*($C$6:$C$58<>"")*($B$6:$B$58=G5),ROW($C6:$C58)-ROW($C$6)+1),2))

or

=INDEX($C$6:$C$58,LARGE(($A$6:$A$58=F5)*($B$6:$B$58=G5)*($C$6:$C$58<>"")*(ROW($C$6:$C$58)-ROW($C$6)+1),2))

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

[#5]

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?

[#6]

@matt0020190 

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.

[#7]

@matt0020190 

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.