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.
I am getting #REF! error please help resolve OliverScheurich
=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.
downloaded your file, looking good but I am still getting #REF! error, could not attach my Excel file to this reply, please guide OliverScheurich
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?
