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
up vote 6 down vote accepted

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

  • 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

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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