# Second to last value from table with condition

Brass Contributor

# 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

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

# Re: Second to last value from table with condition

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

Does this work in your sheet? Enter the formula as an arrayformula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

# Re: Second to last value from table with condition

You need to specify the Excel version.  Otherwise you might get solutions like

``````= MAP({1;2;3;4},
LAMBDA(w,
LET(
criterion, (week=w)*(amount>0),
matches,   FILTER(amount, criterion),
CHOOSEROWS(matches,-2)
)
)
)``````

A solution written for Excel 365 may have very little in common with earlier generations of spreadsheet.

# Re: Second to last value from table with condition

In the screenshot below i've highlighted where you can attach either a file or a screenshot.

# Re: Second to last value from table with condition

You can use the following formula to get the second to last value for a specific week number:

``=INDEX(B:B,MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1)))``

Replace week_number with the week number you're interested in.

This is an array formula that uses the INDEX and MAX functions. Here's how it works:

1. IF(A:A=week_number,ROW(B:B)-ROW(B1)+1) creates an array of row numbers where the week number matches week_number. The ROW(B:B)-ROW(B1)+1 part is used to adjust the row numbers to start at 1, since the formula will return an array starting from the first row.
2. MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1)) finds the maximum row number in the array, which will be the row number of the last non-blank cell in the specified week.
3. INDEX(B:B,MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1))) returns the value in column B at the row number found in step 2.

Make sure to enter this formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. This will add curly braces around the formula in the formula bar to indicate that it's an array formula.

# Re: Second to last value from table with condition

attaching screenshot will not help resolve the issue, please can I not attach the excel file to understand. However, I am attaching link to the file which is residing on my OneDrive.
https://1drv.ms/x/s!Avclfms7aCtXjF0EPX0f_ZClrECh?e=6UTmT3

# Re: Second to last value from table with condition

``=INDEX(Z1412:Z1761,LARGE(IF((D1412:D1761=M1769)*(Z1412:Z1761<>""),ROW(1412:1761)-1411),2))``

Does this return the expected result?

# Re: Second to last value from table with condition

Thank you very much it is working fine. Can you please suggest a formula for last value based on criteria with if condition based on another column, which works on google sheet as you may know Google Sheet does not accept formula with Filter & Aggregate commands

# Re: Second to last value from table with condition

``=INDEX(Z1412:Z1761,LARGE(IF((D1412:D1761=M1769)*(Z1412:Z1761<>""),ROW(1412:1761)-1411),1))``

Does this return the expected result? I only work with Excel and therefore can only suggest solutions for Excel.

# Re: Second to last value from table with condition

Thank you sir, it is working but I need formula with if condition based on another column

# Re: Second to last value from table with condition

Maybe i can help you with this. Which additional condition do you want and on which column should it be based?