Second to last value from table with condition

i tried row() formula to get the row number in the adjacent cell and used this cell's reference as row number in your formula. It is working. OliverScheurich 

SatyaGupta_In 

In this situation i'd resort to the formula suggested earlier.

Hi,My data rows are 1433 - 1788 but after applying above formula it is picking up results outside of this range. Anything further can be done or should I resort to old formula earlier suggested by you. Thanks in advance for your help OliverScheurich 

SatyaGupta_In 

=IFERROR(LARGE(IF((E1:E1788=C1796)*(AB1:AB1788<>""),ROW(1:1788)),2),"")

You are welcome. This formula returns the intended result. The other 2 formulas are in cells G1798 and G1799 in the attached file.

Thanks a lot for the help, however there are three adjacent rows to these value which are row numbers (circled in the attached screenshot) of these values, I hope these would also require changes in these formula OliverScheurich 

SatyaGupta_In 

=INDEX(AB1:AB1788,LARGE(IF((E1:E1788=C1796)*(AB1:AB1788<>""),ROW(1:1788)),2))

You can try this formula. However you still have to adapt the range for example from AB1:AB1788 to AB1:AB2000 and 1:1788 to 1:2000. But you don't have to change the part highlighted in red anymore: ROW(1433:1788)-1432

Hi, Once again I need your help. Infact my file has data in the rows above the data rows for which formula was applied. Because of this the row number keeps changing whenever I add some data rows. Is there any way that the row number in the formula keeps changing relative to the data range of the formula. The screenshot of the formula with the row number in question is circled as also the Excel file in which you earlier suggested the formula. Thanks in advance for your help. OliverScheurich 

Hi, Once again I need your help. In fact my file has data in the rows above the data rows for which formula was applied. Because of this the row number keeps changing as I continue to add some data. Is there any way that the row number in the formula keeps changing relative to the data range of the formula. The screenshot of row number in the formula is circled for your easy reference as also the Excel file in which you did suggest the formula. OliverScheurich 

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.

SatyaGupta_In 

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. 

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 

SatyaGupta_In 

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.

Sir, I'm able to get last value based on column 'L' <>0. Is there any way to get row number of these data (Last Value / Second to Last Value) in another cell, if so please help out. Thanks in advance. OliverScheurich 

SatyaGupta_In 

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

SatyaGupta_In 

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

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