7

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)
Improve this question
3
  • 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, 2014 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, 2014 at 23:43
  • What does the "SAC!` part mean? Is that a reference to a different sheet?
    – Hubert Grzeskowiak
    Jul 9, 2019 at 7:50

3 Answers 3

Reset to default
8

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

Improve this answer
1
  • 1
    QUERY worked like a charm. Thank you!
    – Simone Carletti
    Nov 15, 2014 at 19:39
5

I just had a similar problem and solved it nicely with the MINIFS function:

=MINIFS(SAC!E$2:E$23, SAC!A$2:A$23, ">=" & A2)

In short:

select the minimum value from the factor column, where the value in the depth column is equal to or greater than the given value.

Improve this answer
1
  • 1
    This is awesome. I needed MAXIFS in my case, but this was a perfect way to handle the issue, thanks!
    – Christian Gruber
    Dec 8, 2020 at 18:59
1

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.

Improve this answer
2
  • 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, 2014 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, 2014 at 23:12

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.