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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 12, 2023

matt0020190 

You need to specify the Excel version.  Otherwise you might get solutions like

= MAP({1;2;3;4},
    LAMBDA(w,
      LET(
        criterion, (week=w)*(amount>0),
        matches,   FILTER(amount, criterion),
        CHOOSEROWS(matches,-2)
      )
    )
  )

A solution written for Excel 365 may have very little in common with earlier generations of spreadsheet.

Resources