Forum Discussion
Second to last value from table with condition
You can try these formulas.
=IFERROR(LARGE(IF(($D$1412:$D$1761=$M$1769)*($Z$1412:$Z$1761<>"")*($L$1412:$L$1761<>""),ROW($1412:$1761)-1411),ROW(1:1))+1411,"")This is the formula for the row of the n-th to last value.
=IFERROR(INDEX($Z$1412:$Z$1761,LARGE(IF(($D$1412:$D$1761=$M$1769)*($Z$1412:$Z$1761<>"")*($L$1412:$L$1761<>""),ROW($1412:$1761)-1411),ROW(1:1))),"")This is the formula that returns the corresponding value.
The formulas are in cells L1771 and M1771 and are filled down.
Thank you very much for taking great pain. I don't have enough kind words to express gratitude. I have copied the formula and changed the row numbers due to change in data in my file. The formula suggested by you is there in cell number J1793 (filed with yellow colour) but it is returning nothing. The cell I1793 contains the last value arrived at after applying two conditions on column L & M. The latest file is attached for doing the needful.OliverScheurich
- Thank you very much, I was working on Office 365 but real problems seems to be working on Google Sheet. You are right criteria also needed to be changed which I did but forgot to mention that in my post. Thank you once again for your time & great help
Thank you it worked, in fact I did on Google drive through mobile hence result was not being returned. Now I have done in Excel on Laptop it is working fine. However, what needs to be changed for obtaining second to last row number instead of last row number with same criteria.
We have to enter the formulas as arrayformulas with ctrl+shift+enter if we don't work with Office 365 or Excel for the web or Excel 2021. In the Excel file the search criteria is in cell H1790. In the other file it was in a different cell therefore the formulas have to be adjusted.