Excel 2007 : Return first value below and above search value problem

Nevermind,

Used MAX and MIN

George


Morning

In the attached file the idea is to search for a value (entered in F5) within a range of values and then to return from the range of values the first value lower and higher than the searched for value.

The problem is that this works correctly for some searched values, and incorrectly for others.

So in A2:A14 the values are 0-360 (incremented by 30)

In C2 (should return first lower value) the formula is:

{=INDEX(A2:A14,MATCH(SMALL(ABS(A2:A14-$F$5),ROW(A1)),IF(COUNTIF($C$1:C1,A2:A14)<COUNTIF(A2:A14,A2:A14),ABS(A2:A14-$F$5),"A"),0))}

In C3 (should return first higher value) the formula is:

{=INDEX(A2:A14,MATCH(SMALL(ABS(A2:A14-$F$5),ROW(A2)),IF(COUNTIF($C$1:C2,A2:A14)<COUNTIF(A2:A14,A2:A14),ABS(A2:A14-$F$5),"A"),0))}

Any thoughts on how to fix this problem?

Thanks in advance.

George