# 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
• What does the "SAC! part mean? Is that a reference to a different sheet? – Hubert Grzeskowiak Jul 9 '19 at 7:50

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