# Find the closest value (equal or greater) in a range in Google Spreadsheets

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)
• No. It's just an example, the factor must be taken from the row, not calculated with an expression. In fact, the rule you provided would not work for feet. – Simone Carletti Nov 6 '14 at 23:14
• As I said, the value for B cannot be pre-determined from A, take it as a requirement. – Simone Carletti Nov 6 '14 at 23:43

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),)`

• `QUERY` worked like a charm. Thank you! – Simone Carletti Nov 15 '14 at 19:39

Maybe something like:

``````=IFERROR(index(B:B,match(F4,A:A,0)),index(B5:B100,match(F4,A4:A99)))
``````

where F4 contains the `6`/`10` value.

• 6 and 10 are just examples, the formula should work for any value in the depth range, not just for 6/10. – Simone Carletti Nov 6 '14 at 23:00
• No, it does not. Apart that the fields where completely wrong, I was able to reuse `INDEX` to simplify the formula into `=IF(ISNUMBER(A4),INDEX(SAC!E:F,(MATCH(A4, SAC!E:E, -1)), 2),)`, but it still doesn't fix the problem of the order. If I sort with `0` and I don't use the field sorted in reverse range, it does not select the right value. – Simone Carletti Nov 6 '14 at 23:12