Match Formula - Match Type "-1" not working

[ringmaker]

Hi - I am attempting to use the match formula to find the smallest number in a list that is greater than the number I am referencing. The match_type in the match formula should allow me to do this by using "-1" in the last argument of the formula. Instead of getting the answer I want, I am getting n/a.

Here is a simple example of the data and the formula I am using:

Lookup Value: 5.5 (cell b1)

Data: 1,2,3,4,5,6,7,8,9,10 (cell b3:b12)

Formula: =match(b1,b3:b12,-1)

Answer: = n/a

Expected Answer: 6

Thanks for your help!!!

 

[Andrew Poulsom]

From Help:

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

 

[NBVC]

You can use this formula possibly instead:

=MIN(IF(B3:B12>=B1,B3:B12))

Confirmed with CTRL+SHIFT+ENTER not just ENTER

 

[Aladin Akyurek]

Hi - I am attempting to use the match formula to find the smallest number in a list that is greater than the number I am referencing. The match_type in the match formula should allow me to do this by using "-1" in the last argument of the formula. Instead of getting the answer I want, I am getting n/a.

Here is a simple example of the data and the formula I am using:

Lookup Value: 5.5 (cell b1)

Data: 1,2,3,4,5,6,7,8,9,10 (cell b3:b12)

Formula: =match(b1,b3:b12,-1)

Answer: = n/a

Expected Answer: 6

Thanks for your help!!!

=INDEX($B$3:$B$12,MATCH(B1,$B$3:$B$12,1)+(LOOKUP(B1,$B$3:$B$12)<>B1))

 

[ringmaker]

Wow! Thanks!!!

I posted that question and in minutes, all my problems are solved!!!!

You all are the best!

Thanks,

Ringmaker