get first higher number

[Coaster]

Hi,

I need a function that can get, from a matrix, the first number that is higher than the number in a given cell.

Is this possible?

Thank you

[daddylonglegs]

By matrix what do you mean, a single column or row or a range consisting of multiple row and columns? By "first" I presume you mean positionally on the worksheet

If it's the former, e.g. A2:A100, you can use this formula where the given cell is C2

=INDEX(A2:A100,MATCH(TRUE,INDEX(A2:A100>C2,0),0))

If it's the latter then define "first"

[Coaster]

sorry wasn't very clear..

yes, by matrix I mean a range of cells consisting of multiple rows and columns

by "first" I mean searching this range for the most approximate number but higher than the one specified.

eg:

i have the number 7.6

from this range of numbers: 3.3;4.4;6.7;8.1;10.1;5.5;9.2

i want the function to get me the number 8.1, which is the lowest number from the range that is higher than 7.6

does this make sense?

[c_m]

Try:
=MIN(IF(A1:A7>B1,A1:A7))
confirmed with control+shift+enter

where A1:A7 is the range of numbers and B1 is the reference number

[Coaster]

error: #value

the problem with this is working with ranges of cells and single cells at the same time..the formula you wrote returns a range of cells, not a single cell...

tks anyway

[c_m]

error: #value

the problem with this is working with ranges of cells and single cells at the same time..the formula you wrote returns a range of cells, not a single cell...

tks anyway

can you post a sample workbook?

[Coaster]

sure...here you go...

[c_m]

sure...here you go...

Check the attached file.
The formula needs to be confirmed with control+shift+enter and the array formula doesnt work in merged cells...so what I did is...unmerged the cells, inserted the formula and merged the cells back

[Coaster]

works great now...tnks!

[DonkeyOte]

Why not just use SMALL with COUNTIF ?

J21: =SMALL(G8:R15,COUNTIF(G8:R15,"<="&$J$19)+1)

(non-array)