Lookup Hi / Lo

[chrisabberton]

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

[bj]

an index(match())
combo will probably do whhat you want
=index(range,match(63,range,1))
for value less than 63 and
=index(range,match(63,range,-1))
for vlaue above 63
"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
>
>
> --
> chrisabberton
> ------------------------------------------------------------------------
> chrisabberton's Profile: http://www.excelforum.com/member.php...o&userid=16872
> View this thread: http://www.excelforum.com/showthread...hreadid=474744
>
>

[Richard Buttrey]

You could try

=INDEX(B5:B13,MATCH(C16,B5:B13)+1,1)

HTH

On Mon, 10 Oct 2005 08:49:26 -0500, chrisabberton
<[email protected]> 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

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Domenic]

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
<[email protected]>,
chrisabberton
<[email protected]> 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

[Harlan Grove]

Domenic wrote...
>For the largest value less than or equal to the lookup value...
>
>=LOOKUP(C16,B5:B13)

Since this would only work when B5:B13 were sorted in ascending order,

>For the smallest value greater than or equal to the lookup value..
>
>=MIN(IF(B5:B13>=C16,B5:B13))
....

Richard Buttrey's formula,

=INDEX(B5:B13,MATCH(C16,B5:B13)+1)

would be more efficient, and it wouldn't need array entry.

[Domenic]

In article <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> Richard Buttrey's formula,
>
> =INDEX(B5:B13,MATCH(C16,B5:B13)+1)
>
> would be more efficient, and it wouldn't need array entry.

But the formula would return #N/A if the lookup value was 10. Shouldn't
the correct answer be 14?

[Harlan Grove]

Domenic wrote...
>"Harlan Grove" <[email protected]> wrote:
>>Richard Buttrey's formula,
>>
>>=INDEX(B5:B13,MATCH(C16,B5:B13)+1)
>>
>>would be more efficient, and it wouldn't need array entry.
>
>But the formula would return #N/A if the lookup value was 10. Shouldn't
>the correct answer be 14?

Boundary conditions. I'll grant that when C16 = 10, the smallest value
in the list greater than C16 should be 14 rather than #N/A. However, if
C16 were 300, what should the formula return? 0 (which your formula
does) or #N/A (which provides symmetry with the LOOKUP call when C16 =
10)? If it should return #N/A, then perhaps it should resemble

=IF(C16<B5,B5,INDEX(B5:B13,MATCH(C16,B5:B13)+1))

[Harlan Grove]

Harlan Grove wrote...
....
> . . . If it should return #N/A, then perhaps it should resemble
>
>=IF(C16<B5,B5,INDEX(B5:B13,MATCH(C16,B5:B13)+1))

So maybe I should have made it return #N/A.

=IF(C16<B5,B5,IF(C16>=B13,#N/A,INDEX(B5:B13,MATCH(C16,B5:B12)+1)))

[Domenic]

Yeah, I think the formula should return #N/A. In which case, your
formula would provide the correct result. Thanks Harlan!

In article <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> Boundary conditions. I'll grant that when C16 = 10, the smallest value
> in the list greater than C16 should be 14 rather than #N/A. However, if
> C16 were 300, what should the formula return? 0 (which your formula
> does) or #N/A (which provides symmetry with the LOOKUP call when C16 =
> 10)? If it should return #N/A, then perhaps it should resemble
>
> =IF(C16<B5,B5,INDEX(B5:B13,MATCH(C16,B5:B13)+1))

> So maybe I should have made it return #N/A.
>
> =IF(C16<B5,B5,IF(C16>=B13,#N/A,INDEX(B5:B13,MATCH(C16,B5:B12)+1)))