How can I find the first value greater than or equal to a value in a range sorted in ascending order? Given the range

And a search value like 12.9, 13 or 13.1, how can I find the first value in the range >= the search value?

CodePudding user response：

One can do this with this formula: =IF(ISNUMBER(MATCH(B2,A1:A5,0)), B2, INDEX(A1:A5,MATCH(B2,A1:A5,1) 1,1))

If there is an exact match, use it otherwise then find the less than or equal value (which will be a less than value) and find the next higher value than it.

CodePudding user response：

You can use this formula: =INDEX(A:A,MATCH(ROUNDDOWN(B2) 1,A:A),0)

or use =INDEX(A:A,MATCH(ROUNDUP(B2),A:A),0)

It gets the index of the match of the number rounded down 1 of column A

Ex. 12.9 ROUNDDOWN -> 12 -> 1 -> 13 MATCH -> 4 (row) INDEX(A:A) -> 13

Ex. 12.9 ROUNDUP -> 12 -> 13 MATCH -> 4 (row) INDEX(A:A) -> 13

CodePudding user response：

Where the range of values to INDEX is in column A, and the cell against which you want to MATCH the FIRST value which is TRUE to the condition is B2, you can use formula like this:

=INDEX(A:A,MATCH(TRUE,INDEX(A:A>=B2,0),))

And, to return the cell ADDRESS of the first cell that has a MATCH which is TRUE to the condition, you can use this:

=ADDRESS(MATCH(TRUE,INDEX(A:A>=B2,0),0),1)

To return the minimum value that meets the condition, you can use this:

=MINIFS($A:$A,$A:$A,">="&$B2)

CodePudding user response：

use:

=IFNA(VLOOKUP(B2; A:A; 1; 1))