I have created a locator spreadsheet previously by just finding the closest location via latitude and longitude previously using the following formula. However, now I need to add a second variable need to find the associated National Account then find the closest wholesaler of that specific National Account. I am having trouble implementing the first variable into the array formula below. For example: I want to find the closest locations of National Account "Fast Food 1" and only those entries in the table. The formula below only finds the closest locations via latitude and longitude, which would include Wholesalers not associate with "Fast Food 1".
I linked a image of the sheet I am working on below. the way I have it set up right now is it finds the closest 8 locations and returns the Name of the wholesaler into cells E2-L2. The latitude and longitude in cells C2 and B2 change with a vlookup off of a zip entered on another sheet.
I am stumped on how to first find the associated National Account in cell B2 then find the closest locations of only that National account using their latitude and longitude. Any help would be appreciated.
{=INDEX(Table3[Name], MATCH(SMALL(SQRT((Table3[Latitude]-$C2)^2+(Table3[Longitude]-$D2)^2), E$1), SQRT((Table3[Latitude]-$C2)^2+(Table3[Longitude]-$D2)^2), 0))}
http://imgur.com/a/2ElqE
Bookmarks