software

Hi thereI have a long list of calculations of which i need to find the nearest match.
I can find the nearest elow with VLOOKUP but not the closest.
Have tried.=INDEX(list,MATCH(MIN(ABS(list-target)),(ABS(list-target)),0))
but this returns a #N/A error.
The nature of the spreadsheet means there can be more than one same answer. I only need to return one of these.
Waht is the easiest way to do this???
cheers
nobby
The formula needs to be confirmed with CONTROL SHIFT ENTER, not just
ENTER...

Hope this helps!

In article gt;,
nobbyknownowt gt; wrote:

gt; Hi there
gt; I have a long list of calculations of which i need to find the nearest
gt; match.
gt; I can find the nearest elow with VLOOKUP but not the closest.
gt; Have
gt; tried.=INDEX(list,MATCH(MIN(ABS(list-target)),(ABS(list-target)),0))
gt; but this returns a #N/A error.
gt; The nature of the spreadsheet means there can be more than one same
gt; answer. I only need to return one of these.
gt; Waht is the easiest way to do this???
gt; cheers
gt; nobby

Thanks for replying but that doesnt do it.
See if it helps if I explain a little more!
My list address is r2:r271 and my target is a27.
there are answers in the list calculation I am working on of -21.05 / 15.22 / 22.96 / 30.70 (unfortunately almost 200 of each as I started with a simple calculation!!) they are sorted in order.
=vlookup(a27,r2:r271,1) returns 15.22 (the answer i need is 22.96 a27 total being 22)
=index(r2:r271,match(min(abs(r2:r271-a27)),(abs(r2:r271-a27)),0)) returns #N/A even when i ctrl shift enter
The calc steps follow through as
=index($r$2:$r$271,match(43.054469292902,43.054469 292902,0)
=index($r$2:$r$271,#N/A)
I can see where its wrong but dont know enough about this function to see why? (To be honest cant see how its supposed to work!?amp;£)

I thought i also may be able to remove the multiple entries (the results are identical) but i need a way I can do this that will keep the columns in line and ignore blank entries.

cheers
nobby
I don't know why you're getting #N/A. The formula seems fine and should
return 22.96...

In article gt;,
nobbyknownowt gt; wrote:

gt; Thanks for replying but that doesnt do it.
gt; See if it helps if I explain a little more!
gt; My list address is r2:r271 and my target is a27.
gt; there are answers in the list calculation I am working on of -21.05 /
gt; 15.22 / 22.96 / 30.70 (unfortunately almost 200 of each as I started
gt; with a simple calculation!!) they are sorted in order.
gt; =vlookup(a27,r2:r271,1) returns 15.22 (the answer i need is 22.96 a27
gt; total being 22)
gt; =index(r2:r271,match(min(abs(r2:r271-a27)),(abs(r2:r271-a27)),0))
gt; returns #N/A even when i ctrl shift enter
gt; The calc steps follow through as
gt; =index($r$2:$r$271,match(43.054469292902,43.054469 292902,0)
gt; =index($r$2:$r$271,#N/A)
gt; I can see where its wrong but dont know enough about this function to
gt; see why? (To be honest cant see how its supposed to work!?amp;£)
gt;
gt; I thought i also may be able to remove the multiple entries (the
gt; results are identical) but i need a way I can do this that will keep
gt; the columns in line and ignore blank entries.
gt;
gt; cheers
gt; nobby

nobbyknownowt wrote...
....
gt;My list address is r2:r271 and my target is a27.
....
gt;=index(r2:r271,match(min(abs(r2:r271-a27)),(abs(r2:r271-a27)),0))
gt;returns #N/A even when i ctrl shift enter

The formula above is just what you're typing into your newsgroup
messages, not a copy of what you're entering in Excel. Excel would have
converted all the lower case letters to upper case. Maybe the formula
above is a true copy, but maybe not.

Are there any nonnumeric entries in R2:R271?

gt;The calc steps follow through as
gt;=index($r$2:$r$271,match(43.054469292902,43.05446 9292902,0)
gt;=index($r$2:$r$271,#N/A)
....

You can't step through the calculations. The ABS(R2:R271-A27) term
would return an array that's almost certainly too large for Excel to
display in the formula bar. And, FWIW, MATCH(x,x,0) will always return
#N/A when x is just a number. The 2nd argument to MATCH need to be a
range or an array, so MATCH(x,{x},0) is necessary in order for MATCH to
return 1.

Does the *array* formula
=MATCH(MIN(ABS(R2:R271-A27)),ABS(R2:R271-A27),0) also return #N/A ?Doh!
Embarrassed entry.
Have just found out how to type ctrl shift enter while still in formula bar not on cell.
sorry to waste your valuable time!!