Selecting the smallest negative number

[Coaster]

Hi,

Given a fixed matrix of values, i need a way to calculate the value in that matrix that is closest to a specified number, but always higher than this one.

I hope the excel file will clear things up!

Thanks

[DonkeyOte]

Use SMALL with COUNTIF

=IF(MAX($K$5:$N$11)<=$F$6;"No Values";SMALL($K$5:$N$11;1+COUNTIF($K$5:$N$11;"<="&$F$6)))

[zbor]

Try this comfirmed with ctrl+shift+enter

=F6/MAX(IF(F6/K5:N11<1;F6/K5:N11))

Edit: I'm late Great solution DO!

[Coaster]

thank you very much both of you

[Coaster]

i'm sorry...just one more thing...how can I determine the relative position of the value found? I tried hlookup and vlookup but they're not working

[zbor]

I'm not sure that this is best way... but working for now...

[DonkeyOte]

I generally look to return the "co-ordinates" in a single cell and then if nec. create an Address from the co-ordinates rather than dupe what is in reality an expensive approach

F9:
=MIN(IF(K5:N11=F8,J5:J11+((COLUMN(K5:N5)-COLUMN(K5)+1)/1000000)))
confirmed with CTRL + SHIFT + ENTER

which would return 4.000002

F10:
=INT(F9)&INDEX(K4:N4,ROUND(MOD(F9,1)*1000000,0))

which would return 4B

[DonkeyOte]

coaster, note I posted with incorrect delimiters for you - modify the , to ; as required.

I also took advantage of J5:J11 holding 1 to 7 ... if that were not the case eg A:G and you wanted to return DB rather than 4B then you would use:

F9:
=MIN(IF(K5:N11=F8;ROW(K5:K11)-ROW(J5)+1+((COLUMN(K5:N5)-COLUMN(K5)+1)/1000000)))
confirmed with CTRL + SHIFT + ENTER

F10:
=INDEX(J5:J11;INT(F9))&INDEX(K4:N4;ROUND(MOD(F9;1)*1000000;0))

[Coaster]

that's perfect! thank you very very much