I have 2 tables; Table 1 has the lat/long coordinates for a list of 15,000 Stops and Table 2 has the lat/long coordinates for 450 addresses. My goal is to identify which stop is closest to each of the addresses by using those coordinates. This is how the tables are set up
Table 1: Stops
| Stop # (col A) | Latitude (col B) | Longitude (col C) |
|---|---|---|
| 1 | 40.7182 | -74.00018 |
| 2 | 41.82027 | -87.6032 |
| 3 | 34.05255 | -118.242 |
| 4 | 27.59021 | -80.29301 |
| 5 | 39.82039 | -89.62517 |
Table 2: Addresses
| Address # (col G) | Latitude (col H) | Longitude (col I) |
|---|---|---|
| 1 | 43.05439 | -87.8958 |
| 2 | 44.97301 | -93.0379 |
| 3 | 32.90127 | -98.9361 |
| 4 | 28.74312 | -79.1028 |
| 5 | 33.69102 | -83.1266 |
I've tried a bunch of different combinations, and this is the function that has been the closest:
INDEX(A$2:A$17986, SUMPRODUCT((MIN(SQRT((B$2:B$17986 - H2)^2 + (C$2:C$17986 - I2)^2)) = SQRT((B$2:B$17986 - H2)^2 + (C$2:C$17986 - I2)^2)) * ROW(A$2:A$17986))
The function operates without error, but the results were way off when I manually checked them. I've gone over the formula to see what is off but can't figure it out. Any ideas? Thanks!