find nearest help

N

nobbyknownowt

Hi there
I 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
 
D

Domenic

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER...

Hope this helps!
 
N

nobbyknownowt

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 starte
with a simple calculation!!) they are sorted in order.
=vlookup(a27,r2:r271,1) returns 15.22 (the answer i need is 22.96 a2
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.054469292902,0)
=index($r$2:$r$271,#N/A)
I can see where its wrong but dont know enough about this function t
see why? (To be honest cant see how its supposed to work!?&£)

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

cheers
nobb
 
D

Domenic

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

Harlan Grove

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

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?
The calc steps follow through as
=index($r$2:$r$271,match(43.054469292902,43.054469292902,0)
=index($r$2:$r$271,#N/A)
Click to expand...
....

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 ?
 
N

nobbyknownowt

Hi Harlan
You are correct in that i copy the formula from one pc to another but
it is correct as written.
there are no non numeric entries in r2:r271
the *array* formula
=MATCH(MIN(ABS(R2:R271-A27)),ABS(R2:R271-A27),0) does also return #N/A

does that throw any more light onto it for you?
hope so
cheers
nobby
 
N

nobbyknownowt

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!!
 
Top