Forum Discussion
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.
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.
