 # Second to last value from table with condition

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

6 Replies

# Re: Second to last value from table with condition

=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.

# Re: Second to last value from table with condition

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

# Re: Second to last value from table with condition

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.

# Re: Second to last value from table with condition

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

Is this potentially a formatting issue?

# Re: Second to last value from table with condition

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.

# Re: Second to last value from table with condition

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.