Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jeff
 
Posts: n/a
Default 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.
  #2   Report Post  
tjtjjtjt
 
Posts: n/a
Default

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.

  #3   Report Post  
JulieD
 
Posts: n/a
Default

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.



  #4   Report Post  
tjtjjtjt
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #6   Report Post  
Chip Pearson
 
Posts: n/a
Default

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.



  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 10:04 AM


All times are GMT +1. The time now is 03:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"