1

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

1
  • Did you try =MINIFS() ?
    – pjmg
    Apr 24, 2020 at 6:41

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.