unique closest match formula

[epactheactor]

Hello!

I have 2 columns of data;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mile number[/TD]
[TD]Stop sign's mile number[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]159[/TD]
[/TR]
[TR]
[TD]151[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]152[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]160[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]165[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]178[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm wanting to create a formula which would show/count the closest match of the signs to the mile number.
It would look like this;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mile number[/TD]
[TD]Signs[/TD]
[TD]Sign's mile number[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]0[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]0[/TD]
[TD]159[/TD]
[/TR]
[TR]
[TD]151[/TD]
[TD]1[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]152[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]160[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]165[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]171[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]176[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]178[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm attempted to use Match and Index, but it isn't working for me. Here's what I've worked out so far.

=(IF(INDEX(FC,MATCH(MIN(ABS(FC-E480)),ABS(FC-E480),))>E480,"",1)) Where FC is the range.

Is such a formula even possible?

 

[Gerald Higgins]

Why is the answer "2" for mile 160 ?

Is it because the 2nd sign is closer to mile 160 than any other mile number ?
Or is it because there are 2 signs that are close to mile 160 ?

 

[Finalfight40]

how about something like this adjusting ranges to suit.

=IF(NOT(ISNUMBER(A1)),COUNTIF($C$2:$C$4,"<="&A2),IF(NOT(ISNUMBER(A3)),COUNTIF(C1:C3, ">=" & A2),COUNTIFS($C$2:$C$4,">="&((A2-A1)/2)+A1,$C$2:$C$4,"<"&((A3-A2)/2)+A2)))

Also Gerald i got curious and tested your signature. Its awesome.

 

[Gerald Higgins]

Thanks !

 

[Finalfight40]

Forgot to fix one of my ranges:

=IF(NOT(ISNUMBER(A1)),COUNTIF($C$2:$C$4,"<="&A2),IF(NOT(ISNUMBER(A3)),COUNTIF($C$2:$C$4, ">=" & A2),COUNTIFS($C$2:$C$4,">="&((A2-A1)/2)+A1,$C$2:$C$4,"<"&((A3-A2)/2)+A2)))

 

[Finalfight40]

Looking at this again today, i made a small mistake which i've now corrected if you are using this.

=IF(NOT(ISNUMBER(A1)),COUNTIF($C$2:$C$4,"<"&((A3-A2)/2)+A2),IF(NOT(ISNUMBER(A3)),COUNTIF($C$2:$C$4, ">=" & ((A2-A1)/2)+A1),COUNTIFS($C$2:$C$4,">="&((A2-A1)/2)+A1,$C$2:$C$4,"<"&((A3-A2)/2)+A2)))