Forum Discussion

matt0020190's avatar
matt0020190
Brass Contributor
Dec 13, 2021

Second to last value from table with condition

Hi experts   Can you help me with the below?   I have a list of cash values in a table, example as follows by a rotating week number (1-4).   A ------------ B 1 ----------- 40 2------------- ...
excel
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Mar 24, 2023

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.

 

SatyaGupta_In's avatar
SatyaGupta_In
Brass Contributor
May 03, 2023

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 

  • SatyaGupta_In's avatar
    SatyaGupta_In
    Brass Contributor
    May 05, 2023

    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's avatar
    SatyaGupta_In
    Brass Contributor
    May 04, 2023
    Okay, thanks for your support and help
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 04, 2023

    SatyaGupta_In 

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

  • SatyaGupta_In's avatar
    SatyaGupta_In
    Brass Contributor
    May 04, 2023

    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's avatar
    SatyaGupta_In
    Brass Contributor
    May 03, 2023
    Thank you very much
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 03, 2023

    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.

     

  • SatyaGupta_In's avatar
    SatyaGupta_In
    Brass Contributor
    May 03, 2023

    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 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 03, 2023

    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