Finding the nearest values? 1

[jmw]

I'm having a problem here with finding a simple function (that does not involve me messing with nested IF functions) to find the nearest values from some data.

A1 contains my value (number, two decimal places).

A2 to A11 contains a list of values in ascending order.

In A12 I want to put the nearest value less than A1's value and in A13 the nearest value that is greater than A1's value.

In the event I get an exact match (unlikely but possible) I need to know what will happen in A12 and A13 so that can then responding with an IF function and modify the subsequent calculations.

I've been trying to figure this out using Hlookup and Index functions but the help directions seem to have been written by Martians and the examples are crap.

Some gentle help for idiots required please.

PS this should be automatic and not require special keystrokes. It should also, if possible, avoid add-ins.
This spreadsheet will be distributed to others to use.
I am writing it in Excel 2002.

JMW

http://www.ViscoAnalyser.com

 

[IDS]

In B1 put =MATCH(A1,A2:A11)
In A12 put =INDEX($A$2:$A$11,$B$1)
In A13 put =INDEX($A$2:$A$11,$B$1+1)

If you get an exact match it will appear in A12, and A13 will be the next number in the list.

You could also use a lookup function, but for a list in a column you want VLOOKUP, not HLOOKUP.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[jmw]

Thanks Doug, I'm going to try that solution next.
In the meanwhile I have searched all the learned sites.
Most seem to use some sort of INDEX(ABS(MIN(****) function that require cntl+shift+enter
However, the solution that works to find the higher and lower numbers best, so far, I found here:

http://www.exceltip.com/st/Retrievi..._a_List_when_there_is_No_Exact_Match/993.html

This works a treat.
I now have to add in some IF functions for where the value entered is either below the lowest value in my range or above the highest number in my range and the exact match situation.

JMW

http://www.ViscoAnalyser.com

 

[jmw]

Thanks Doug, that is an elegant solution also. Now I have to work out which one will best suit the subsequent operations. I suspect yours because of that initial =MATCH(A1,A2:A11)
which gives me an index to work with.
Thanks again.

JMW

http://www.ViscoAnalyser.com

 

[IDS]

jmw - the formulas given in your link will work on an unsorted list. If you know the list will always be sorted using =MATCH is simpler. In fact that is exactly what it is for.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[jmw]

Thanks again IDS, that is not something I had noticed because my data is ordered.

Many of the "expert" help sites so often come up with no real answers to problems.

In this case I was lucky to be able to learn of both solutions (neither of which requires "cntl+shift+enter") on the same day I posed the question.

Always satisfying to push ones own envelope and learn new things, and comforting to know there is good help at hand.





JMW

http://www.ViscoAnalyser.com