Google-sheets – In Google Sheets,how to get a value lower than a particular cell value in a row

google sheetsgoogle-sheets-custom-functionworksheet-function

In Google Sheets, I have numbers in 12 cells in a row. Then in the 13th cell of the same row, I have a number which is one of these 12 numbers. Now, I want find the 1st value lower than the value in the 13th cell going from right to left. Example:

2020 2090 2201 2393 2273 2159 2105 2200 2150 2199 2225 2290 2159

2159 is 13th cell value, which is in the 6th cell and the 1st lower value before that is 2090. I want this value in a cell in that row. The 13th cell value can be any of the 1st 12 cell values (not necessarily in 6th cell).

Best Answer

Try:

=max(filter(INDIRECT("A6:"&ADDRESS(MATCH(M6,A6:L6,0),row(M6)-1, 4)),INDIRECT("A6:"&ADDRESS(MATCH(M6,A6:L6,0),row(M6)-1, 4))<M6))

(assumes that the data is on row#6)

The logical sequence is:

  • match - match the value in column M with the range of 12 columns to the left (A:L). This returns the column number.
  • address - build the cell address for the matching value, but use the column one cell to the left (this will become the right-hand end of a range).
  • indirect - return the range values from cell A6 to the "address"
  • filter - filter the range, and set the condition for range values less than the value in column M
  • max - return the maximum value of the remaining values.
Related Topic