Forum Discussion
Second to last value from table with condition
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
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.
- 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?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.
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.
