Lookup closest number in list

Hi

I have a list of numbers in column "B" and I also have a number in cell A1.

I want to get the closest number in column B to the value in A1 using a
formula,
is this possible?

Thanks for your help.

Use VLOOKUP.
For example, if your list in Column B is in B1:B5:

=VLOOKUP(A1,$B$1:$B$5,1,1)

tj

"Jeff" wrote:

> Hi
>
> I have a list of numbers in column "B" and I also have a number in cell A1.
>
> I want to get the closest number in column B to the value in A1 using a
> formula,
> is this possible?
>
> Thanks for your help.

Hi Jeff

you can use the Lookup function if the numbers in B are sorted ascending
=LOOKUP(A1,B1:B100)

Cheers
JulieD

"Jeff" > wrote in message
...
> Hi
>
> I have a list of numbers in column "B" and I also have a number in cell
> A1.
>
> I want to get the closest number in column B to the value in A1 using a
> formula,
> is this possible?
>
> Thanks for your help.

Forgot to Add, VLOOKUP will always returns the biggest value in Column B that
is still smaller than your LOOKUP value.

See:
http://www.contextures.com/xlFunctions02.html

For more details.

tj

Another interpretation:

=INDEX(B1:B99,MATCH(1,--(ABS(B1:B99-A1)=MIN(ABS(B1:B99-A1))),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Jeff wrote:
>
> Hi
>
> I have a list of numbers in column "B" and I also have a number in cell A1.
>
> I want to get the closest number in column B to the value in A1 using a
> formula,
> is this possible?
>
> Thanks for your help.

--

Dave Peterson

See the "Closest Match Lookups" section at
http://www.cpearson.com/excel/lookups.htm .

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jeff" > wrote in message
...
> Hi
>
> I have a list of numbers in column "B" and I also have a number
> in cell A1.
>
> I want to get the closest number in column B to the value in A1
> using a
> formula,
> is this possible?
>
> Thanks for your help.

After looking at Chip's response, I like this better:

=INDEX(B1:B99,MATCH(MIN(ABS(B1:B99-A1)),ABS(B1:B99-A1),0))

Still array entered, though.

Dave Peterson wrote:
>
> Another interpretation:
>
> =INDEX(B1:B99,MATCH(1,--(ABS(B1:B99-A1)=MIN(ABS(B1:B99-A1))),0))
>
> This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> correctly, excel will wrap curly brackets {} around your formula. (don't type
> them yourself.)
>
> Jeff wrote:
> >
> > Hi
> >
> > I have a list of numbers in column "B" and I also have a number in cell A1.
> >
> > I want to get the closest number in column B to the value in A1 using a
> > formula,
> > is this possible?
> >
> > Thanks for your help.
>
> --
>
> Dave Peterson

--

Dave Peterson