find nearest help
 

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

find nearest help
 

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

Hope this helps!

In article ,
nobbyknownowt wrote:

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

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!?&£)

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

find nearest help
 

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

In article ,
nobbyknownowt wrote:

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!?&£)

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

find nearest help
 

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

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.05446 9292902,0)
=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!!