I’m working with a list of Lat/Long coordinates for fuel stations and trying to find how many of these stations are within 5 miles of a given location.

We are needing to find how many Alternate stations are within 5 miles of the standard fuel station.

We’ve been trying multiple formulas to get these values but have not had any success.

The below formula provided the distance from a designated fuel station to an alternative station:

=IFERROR(INDEX(ACOS(COS(RADIANS(90-RD_Stations!$C$3:$C$5000))*COS(RADIANS(90-$R3))+SIN(RADIANS(90-RD_Stations!$C$3:$C$5000))*SIN(RADIANS(90-$R3))*COS(RADIANS(RD_Stations!$D$3:$D$5000-$S3)))*6371,MATCH(SMALL((ABS($R3-RD_Stations!$C$3:$C$5000)^2+ABS($S3-RD_Stations!$D$3:$D$5000)^2)^(0.5),1),(ABS($R3-RD_Stations!$C$3:$C$5000)^2+ABS($S3-RD_Stations!$D$3:$D$5000)^2)^(0.5),0)),0)

Now we tried using the below formula to find count of records less than 5 miles from the original fuel station:

=SMALL((ABS($R3-RD_Stations!$C$3:$C$5000)^2+ABS($S3-RD_Stations!$D$3:$D$5000)^2)^(0.5),1)

I’ve been able to pull individual distances but could not find a way to get a rank of closest to farthest with a maximum value of 5 miles.