Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number formats that allow you to format the appearance of negativ. | Charts and Charting in Excel |