Google-sheets – Find the closest value (equal or greater) in a range in Google Spreadsheets

formulasgoogle sheets

I have a range with a list of values (depths). I'd like to be able to select the row where the depth matches a value in a cell I provide. If no value matches, the greater value should be selected.

Here's the range.

The solution I found, is to have a temporary area where I revert the order of the range, then I use MATCH to take advantage of the -1 sort parameter.

Last but not least, I use INDIRECT to extract the value of a cell in the same row, but a different column.

=IF(ISNUMBER(A2),INDIRECT("SAC!F"&(MATCH(A2, SAC!$E$1:$E$23, -1))),)

Is there a better way to achieve this result?

Examples:

  • If the cell has value 6 (exact match), I'd like to get 1.6 (same row)
  • If the cell has value 10 (no match), I'd like to get 2.2 (round to 12, then get the factor)

Best Answer

Two options to try:

=IF(ISNUMBER(A2),INDEX(SORT(SAC!F:F,SAC!E:E,0),MATCH(A2,SORT(SAC!E:E,SAC!E:E,0),-1)),)

=QUERY(SAC!E:F,"select F where E >= "&A2&" order by E limit 1",0)

It's a shame that VLOOKUP doesn't accept the -1 sort parameter, otherwise this would work:

=IF(ISNUMBER(A2),VLOOKUP(A2,SORT(SAC!E:F,1,0),2,-1),)

Related Topic