unique closest match formula

[epactheactor]

Hello!

I have 2 columns of data;

Mile number	Stop sign's mile number
111	150
123	159
151	160
152
160
165
171
176
178

<tbody>
</tbody>

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;

Mile number	Signs	Sign's mile number
111	0	150
123	0	159
151	1	160
152	0
160	2
165	0
171	0
176	0
178	0

<tbody>
</tbody>

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)))