Hello Excel Experts,
I have a situation where I need to do a loop search and from it, to give me the closest distance.
In the example below, I would use a make up address, latitude and longitude - 123 Excel Rd, Vancouver, British Columnbia. Latitude: 123. Longitude: 789
So the situation is:
1. On Sheet1, do a vlookup. If address in Sheet1 (123 Excel Rd) is found on Sheet2, take the latitude and longitude on Sheet2 and "paste" it in Sheet1 on the specified column (i.e column E and column F).
2. If address is not found on Sheet2, look at the city of the address (Vancouver) and, on Sheet2, calculate the longitude and latitude of cities (Vancouver) and based on it's longitude and latitude, check which longitude and latitude has the shortest distance from 123 Excel Rd. When found, "paste" it in Sheet1 on the specified column.
3. If city (Vancouver) were not to be found on Sheet2, Loop Through the whole Sheet2 and find the closest/shortest distance from 123 Excel Rd and "paste" it on Sheet1 of the specified column AND Fill the Cell with Red.
Your help is greatly appreciated!!!
Bookmarks