Hello everyone, I've got a data set of temperature recording stations, their lat / lon, and a list of zip codes with their corresponding geographic center lat / lon. I've got a function that will find the closest station for a given zip code, but for a number of reasons the closest may not be the best choice, so I would like to also return the second and third closest stations as well. I don't intend for this data to really be interacted with by anyone using the full workbook so I don't want to set it up as a pivot and make everyone manually transcribe information over.
I've got a nice function that when I give it lat / lon values from my zip code list will search through the list of stations and show the nearest, now I'd just like to somehow show the next two closest.
=INDEX($D$4:$D$1890,MATCH(MIN(SQRT(($E$4:$E$1890-R4)^2+($G$4:$G$1890-S4)^2)),SQRT(
($E$4:$E$1890-R4)^2+($G$4:$G$1890-S4)^2),0),1)
*Note: the zip code data was removed and just shown as the locations on the few station locations included so the formulas evaluate correctly.
Bookmarks