For the largest value less than or equal to the lookup value...

=LOOKUP(C16,B5:B13)

For the smallest value greater than or equal to the lookup value..

=MIN(IF(B5:B13=C16,B5:B13))

....confirmed with CONTROL+SHIFT+ENTER,not just ENTER.

Hope this helps!

In article
,
chrisabberton
wrote:

I'm trying to do what i think is quite a simple function.

I have a data array (B5:B13) with the following values in it:

14
40
45
59
70
79
90
100
280

my reference cell is C16 and is currently set to 63.

I would like cell C18 to display the closest match below 63 (in this
example 59), i have acheived this with the forumula
=LOOKUP(C16,B5:B13,B5:B13).

But i want cell C19 to display the closest value above 63 i.e. 70.

I have tried the offset function but can't appear to get it to work,
and would prefer a lookup function if possible.

Thanks in advance,

Chris