looking up figures in a column and selecting next highest match

How do I create a function tolook at a column of figures and select
either the match or the next highest. So if I have a column called
'current' and want to create a column called 'new', the function needs
to look at 'current', compare it with a list of figures and put the
closest higher figure from that list into the 'new' column.

Thanks
Jane

Let's say the list is in a named range called "List".

If your "current" column begins in A1 and the "new" column begins in B1,
enter the following formula in B1:

=A1+MIN(IF(List>=A1,List-A1))

as an array formula with <Ctrl> <Shift> <Enter>. Copy down as needed.

I'm sure there's a much simpler way but I can't figure one out at the
moment.

--

Vasant




<[email protected]> wrote in message
news:[email protected]...
> How do I create a function tolook at a column of figures and select
> either the match or the next highest. So if I have a column called
> 'current' and want to create a column called 'new', the function needs
> to look at 'current', compare it with a list of figures and put the
> closest higher figure from that list into the 'new' column.
>
> Thanks
> Jane
>

Vasant Nanavati wrote...
>Let's say the list is in a named range called "List".
>
>If your "current" column begins in A1 and the "new" column begins in B1,
>enter the following formula in B1:
>
>=A1+MIN(IF(List>=A1,List-A1))
....

An alternative,

=MAX(A1,MIN(IF(List>=A1,List)))

which is a bit more explicit that it returns A1 when A1 is strictly
greater than any values in List.

Hi Harlan:

I believe my formula does that too, unless I'm missing something (been known
to happen).
--

Vasant


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Vasant Nanavati wrote...
>>Let's say the list is in a named range called "List".
>>
>>If your "current" column begins in A1 and the "new" column begins in B1,
>>enter the following formula in B1:
>>
>>=A1+MIN(IF(List>=A1,List-A1))
> ...
>
> An alternative,
>
> =MAX(A1,MIN(IF(List>=A1,List)))
>
> which is a bit more explicit that it returns A1 when A1 is strictly
> greater than any values in List.
>