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

=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 06, 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 05, 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

Resources