If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Lookup closest number in list
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Lookup closest number in list

#1
November 26th 04, 03:33 PM
 Jeff external usenet poster Posts: n/a
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?

#2
November 26th 04, 03:41 PM
 tjtjjtjt external usenet poster Posts: n/a

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?
>

#3
November 26th 04, 03:42 PM
 JulieD external usenet poster Posts: n/a

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?
>

#4
November 26th 04, 03:43 PM
 tjtjjtjt external usenet poster Posts: n/a

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
#5
November 26th 04, 03:59 PM
 Dave Peterson external usenet poster Posts: n/a

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?
>

--

Dave Peterson
#6
November 26th 04, 04:09 PM
 Chip Pearson external usenet poster Posts: n/a

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?
>

#7
November 26th 04, 07:27 PM
 Dave Peterson external usenet poster Posts: n/a

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

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post number formats that allow you to format the appearance of negativ. jassmon Charts and Charting in Excel 1 January 15th 05 09:04 AM

All times are GMT +1. The time now is 02:13 AM.