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 |
All times are GMT +1. The time now is 08:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com