Calculating shortest to longest distances between Zip Codes (using latitude and longitude)

[pawcoyote]

Hi, I have what I believe might be an interesting one I could use some help on.

I've have been working on formulas to show the distances (Miles) between multiple longitude and latitudes (Zip Codes). I am trying to find the shortest distance between multiple Zip Codes. Then put in order the shortest distance to longest distance. I would like to show who is assigned as the shortest distance to longest distances on the Planning worksheet. I tried to post all that I could think to help.

On the Formula worksheet I have the following formulas.

Step1: I created a Count Column that shows how many possible Zip Codes that could be included up to five (5).

=COUNTIF(TEAM!$A$2:$A$57,Planning!$E2)

Step2: I created Ref Columns 1-5 to show which Zip Code is has the shortest distance:

=IF(COLUMNS($G2:G2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=InventoryPlanning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:G2))))

Step3: I created a Distance formula that will show the distances: I also noticed that the distances are estimated not actual. I have tried the Mileage number: 3963.19 and Kilometer number: 6378.135:

=IF($G2="","",IF($E2=$N2,0,ACOS(COS(RADIANS(90-$L2))*COS(RADIANS(90-$C2))+SIN(RADIANS(90-$L2))*SIN(RADIANS(90-$C2))*COS(RADIANS($M2-$D2)))*6378.135))

This formula is successfully returning the correct distances in Distance-1 thru Distance-5. But the the Ref#’s are not showing in the order of shortest distance. It is shown by the State and how I have them loaded on the TEAM worksheet. How can I change the formula above to show the shortest distance in order within the Ref Columns? I would really like to do away with looking at the State first and just look at the shortest distance between the Zip Codes (Latitude/Longitudes). That way I do not have to worry about loading States into the Team sheet just the people and their locations.

Can anyone help? Sorry for the complicated problem in advance.

Planning Worksheet:

Zip Code Distances.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Location Number	Location Name	Address	CITY	STATE	ZIP CODE	Min - Dist	Min Zip	Distance	Ricoh / Client	First Name	Last Name	Zip	Ref2 - Distance	Ref2 - Name	Ref2 - Zip Code	Ref3 - Distance	Ref3 - Name	Ref3 - Zip Code	Ref4 - Distance	Ref4 - Name	Ref4 - Zip Code	Ref5 - Distance	Ref5 - Name	Ref5 - Zip Code	SSSIG	Distance Allowed: Enter Here ---->	65	Client Name: Enter Here ----->	Customer
2			123 Smith Street	MELVILLE	NY	11747	48	10017	48	SMITH	ANDREW	SMITH	10017
3			143 Fargo CT	Vinita	CA	92606	19	92692	19	JONES	VINITA	JONES	92692													JONES
4			13 Callen St	Vinita	CA	92817	30	92692	30	JONES	VINITA	JONES	92692													JONES
5			2 Box Rd	Vinita	CA	91932	125	92692	125	Customer	VINITA	JONES	92692													JONES
6			500 West St	In Between	CA	90712	50	91402	50	APPLE	THOMAS	APPLE	91402													APPLE
7			80 Finn Rd	In Between	CA	90801	53	92692	53	JONES	VINITA	JONES	92692													JONES
8			999 Cargo Rd	Thomas	CA	90001	34	91402	34	APPLE	THOMAS	APPLE	91402													APPLE
9			3 Blue St	Thomas	CA	93001	85	91402	85	Customer	THOMAS	APPLE	91402													APPLE
10			55 Hartford Ave	Thomas	CA	91364	16	91402	16	APPLE	THOMAS	APPLE	91402													APPLE
11			3 Providence Ave	Thomas	CA	91001	29	91402	29	APPLE	THOMAS	APPLE	91402													APPLE
12			5 June Rd	In Between	CA	92364	300	92692	300	Customer	VINITA	JONES	92692													JONES
13			90 Cutler Blvd	In Between	CA	91714	51	91402	51	APPLE	THOMAS	APPLE	91402													APPLE
14			143 Ohio Ave	Providence	RI	02816	26	02809	26	SMITH	DAVID	SMITH	02809													SMITH
15			99 June LN	Okalahoma City	OK	73108	55,000			Customer
16			74 Washington St	Albuquerque	NM	87109	55,000			Customer
17			26 Morgan Rd	Bessemer	AL	35022	55,000			Customer
Planning

Cell Formulas
Range	Formula
G2:G17	G2	=MIN(Formulas!O2,Formulas!S2,Formulas!W2,Formulas!AA2,Formulas!AE2)
H2:H17	H2	=IF($G2=Formulas!$O2,Formulas!$G2,IF($G2=Formulas!$S2,Formulas!$H2,IF($G2=Formulas!$W2,Formulas!$I2,IF($G2=Formulas!$AA2,Formulas!$J2,IF($G2=Formulas!$AE2,Formulas!$K2,"")))))
I2:I17	I2	=IF(G2<5000,G2,"")
J2:J17	J2	=IF($I2<$AB$1,$L2,$AD$1)
K2:K17	K2	=XLOOKUP($H2,TEAM!$B$2:$B$88,TEAM!$C$2:$C$88,"",0)
L2:L17	L2	=XLOOKUP($H2,TEAM!$B$2:$B$88,TEAM!$D$2:$D$88,"",0)
M2:M17	M2	=XLOOKUP($H2,TEAM!$B$2:$B$88,TEAM!$B$2:$B$88,"",0)
Z2:Z17	Z2	=XLOOKUP($H2,TERRITORY!$B$2:$B$86,TERRITORY!$D$2:$D$86,"",0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J:J	Cell Value	contains "Customer"	text	NO
A:A	Cell Value	duplicates	text	NO
I1:I17,K2:Z17,K1:M1,Z1	Cell Value	=""	text	NO

Formula Worksheet:

Zip Code Distances.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	STATE	ZIPCODE	LAT	LONG	LAT+LONG	COUNT	Ref1	Ref2	Ref3	Ref4	Ref5	LAT	LONG	LAT+LONG	DISTANCE-1	LAT	LONG	LAT+LONG	DISTANCE-2	LAT	LONG	LAT+LONG	DISTANCE-3	LAT	LONG	LAT+LONG	DISTANCE-4	LAT	LONG	LAT+LONG	DISTANCE-5
2	NY	11747	40.7946	-73.403	-32.6084	5	10017	06482	19440	02809	02861	40.7517	-73.9707	-33.219	48	41.4087	-73.2485	-31.8398	70	40.2778	-75.2975	-35.0197	170	41.6825	-71.2676	-29.5851	204	41.8814	-71.356	-29.4746	210
3	CA	92606	33.6951	-117.8224	-84.1273	3	91402	92692	94507			34.2262	-118.447	-84.2208	83	33.6144	-117.6433	-84.0289	19	37.8537	-122.0229	-84.1692	598				55,000				55,000
4	CA	92817	33.8512	-117.7915	-83.9403	3	91402	92692	94507			34.2262	-118.447	-84.2208	73	33.6144	-117.6433	-84.0289	30	37.8537	-122.0229	-84.1692	587				55,000				55,000
5	CA	91932	32.5783	-117.1148	-84.5365	3	91402	92692	94507			34.2262	-118.447	-84.2208	221	33.6144	-117.6433	-84.0289	125	37.8537	-122.0229	-84.1692	737				55,000				55,000
6	CA	90712	33.8512	-118.1457	-84.2945	3	91402	92692	94507			34.2262	-118.447	-84.2208	50	33.6144	-117.6433	-84.0289	53	37.8537	-122.0229	-84.1692	566				55,000				55,000
7	CA	91001	34.1912	-118.1392	-83.948	3	91402	92692	94507			34.2262	-118.447	-84.2208	29	33.6144	-117.6433	-84.0289	79	37.8537	-122.0229	-84.1692	537				55,000				55,000
8	CA	92364	35.4667	-115.2722	-79.8055	3	91402	92692	94507			34.2262	-118.447	-84.2208	321	33.6144	-117.6433	-84.0289	300	37.8537	-122.0229	-84.1692	659				55,000				55,000
9	CA	91714	34.0197	-117.9587	-83.939	3	91402	92692	94507			34.2262	-118.447	-84.2208	51	33.6144	-117.6433	-84.0289	54	37.8537	-122.0229	-84.1692	562				55,000				55,000
10	RI	02816	41.6914	-71.5768	-29.8854	2	02861	02809				41.8814	-71.356	-29.4746	28	41.6825	-71.2676	-29.5851	26				55,000				55,000				55,000
11	OK	73108	35.4445	-97.5619	-62.1174	0													55,000				55,000				55,000				55,000
12	NM	87109	35.1506	-106.569	-71.4184	0													55,000				55,000				55,000				55,000
13	AL	35022	33.3224	-86.9657	-53.6433	0													55,000				55,000				55,000				55,000
Formulas

Cell Formulas
Range	Formula
A2:B13	A2	=Planning!E2
C2:C13	C2	=XLOOKUP(Planning!F2, ZipCodeDB!$B$2:$B$50000, ZipCodeDB!$G$2:$G$50000,,0)
D2:D13	D2	=XLOOKUP(Planning!$F2, ZipCodeDB!$B$2:$B$50000, ZipCodeDB!$H$2:$H$50000,,0)
E2:E13	E2	=IFERROR($C2+$D2,"")
F2:F13	F2	=COUNTIF(TEAM!$A$2:$A$57,Planning!$E2)
G2:G13	G2	=IF(COLUMNS($G2:G2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:G2))))
H2:H13	H2	=IF(COLUMNS($G2:$H2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:$H2))))
I2:I13	I2	=IF(COLUMNS($G2:$I2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:$I2))))
J2:J13	J2	=IF(COLUMNS($G2:$J2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:$J2))))
K2:K13	K2	=IF(COLUMNS($G2:$K2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:$K2))))
L2:L13	L2	=XLOOKUP($G2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
M2:M13	M2	=XLOOKUP($G2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
N2:N13	N2	=IF($G2="","",SUM($L2+$M2))
O2:O13	O2	=IF($G2="","",IF($E2=$N2,0,ACOS(COS(RADIANS(90-$L2))*COS(RADIANS(90-$C2))+SIN(RADIANS(90-$L2))*SIN(RADIANS(90-$C2))*COS(RADIANS($M2-$D2)))*6378.135))
P2:P13	P2	=XLOOKUP($H2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
Q2:Q13	Q2	=XLOOKUP($H2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
R2:R13	R2	=IF($H2="","",SUM($P2+$Q2))
S2:S13	S2	=IF($H2="",55000,IF($E2=$R2,0,ACOS(COS(RADIANS(90-$P2))*COS(RADIANS(90-$C2))+SIN(RADIANS(90-$P2))*SIN(RADIANS(90-$C2))*COS(RADIANS($Q2-$D2)))*6378.135))
T2:T13	T2	=XLOOKUP($I2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
U2:U13	U2	=XLOOKUP($I2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
V2:V13	V2	=IF($I2="","",SUM($T2+$U2))
W2:W13	W2	=IF($I2="",55000,IF($E2=$V2,0,ACOS(COS(RADIANS(90-$T2))*COS(RADIANS(90-$C2))+SIN(RADIANS(90-$T2))*SIN(RADIANS(90-$C2))*COS(RADIANS($U2-$D2)))*6378.135))
X2:X13	X2	=XLOOKUP($J2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
Y2:Y13	Y2	=XLOOKUP($J2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
Z2:Z13	Z2	=IF($J2="","",SUM($X2+$Y2))
AA2:AA13	AA2	=IF($J2="",55000,IF($E2=$Z2,0,ACOS(COS(RADIANS(90-$X2))*COS(RADIANS(90-$C2))+SIN(RADIANS(90-$X2))*SIN(RADIANS(90-$C2))*COS(RADIANS($Y2-$D2)))*6378.135))
AB2:AB13	AB2	=XLOOKUP($K2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
AC2:AC13	AC2	=XLOOKUP($K2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
AD2:AD13	AD2	=IF($K2="","",SUM($AB2+$AC2))
AE2:AE13	AE2	=IF($K2="",55000,IF($E2=$AD2,0,ACOS(COS(RADIANS(90-$AB2))*COS(RADIANS(90-$C2))+SIN(RADIANS(90-$AB2))*SIN(RADIANS(90-$C2))*COS(RADIANS($AC2-$D2)))*6378.135))

Team Worksheet:

Book1
	A	B	C	D
1	STATE COVERAGE	ZIP	FIRST NAME	LAST NAME
2	AZ	85283	STEVEN	JONES
3	AZ	85085	ROBERT	SMITH
4	CA	91402	THOMAS	APPLE
5	CA	92692	VINITA	JONES
6	CA	94507	SHANE	SMITH
7	CO	80015	THERESA	JONES
8	CO	80550	CASEY	SMITH
9	CT	02861	BOB	SMITH
10	CT	06482	THOMAS	SMITH
11	CT	02809	DAVID	SMITH
12	GA	33019	BILL	SMITH
13	IL	46311	JOB	SMITH
14	IL	60563	BYRON	SMITH
15	IL	60610	STEVE	JONES
16	IN	46311	JOB	SMITH
17	KY	41091	MARCUS	SMITH
18	MA	02861	BOB	SMITH
19	MA	02809	DAVID	SMITH
20	ME	02861	BOB	SMITH
21	MI	48323	JOEL	SMITH
22	MI	46311	JOB	SMITH
23	MO	64118	LOUIE	SMITH
24	NC	28031	JAMES	SMITH
25	NH	02861	BOB	SMITH
26	NJ	19440	LARRY	SMITH
27	NV	89431	JEFFREY	SMITH
28	NY	10017	ANDREW	SMITH
29	NY	06482	THOMAS	SMITH
30	NY	19440	LARRY	SMITH
31	NY	02809	DAVID	SMITH
32	NY	02861	BOB	SMITH
33	OH	45420	ANDRE	SMITH
34	OH	48323	JOEL	SMITH
35	PA	19440	LARRY	SMITH
36	PA	19006	DAVID	SMITH
37	PA	19341	ERIC	SMITH
38	PA	19341	BRIAN	SMITH
39	RI	02861	BOB	SMITH
40	RI	02809	DAVID	SMITH
41	TX	77382	BRANDON	SMITH
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	Cell Value	duplicates	text	NO

 

[KRice]

Glad to hear. Yes, many of the variable assignments inside the LET function are not strictly necessary; however, doing so (e.g., in the case of baselat, baselong, zip, fname, lname) makes the formula easier to understand, and in cases where those values are needed several times, redundancy of some formulas can be reduced.

 

[pawcoyote]

Glad to hear. Yes, many of the variable assignments inside the LET function are not strictly necessary; however, doing so (e.g., in the case of baselat, baselong, zip, fname, lname) makes the formula easier to understand, and in cases where those values are needed several times, redundancy of some formulas can be reduced.

Going to mark this as complete. Going to see if I can build this within VBA as well. Learning as I move forward. I greatly appreciate all your help!

 

[KRice]

Happy to help…thanks for the feedback.

 

[pawcoyote]

Happy to help…thanks for the feedback.

Hi, I am working on trying to put this into VBA would I post any help requests under this post since it is almost a part of this question, or would I have to create a totally new post? Thanks for your guidance. I do not want to break the rules of the forum.

 

[KRice]

I would recommend beginning a new post and mention in it that you previously worked through the forum to develop a formula-based solution to this problem, but now you'd like some help achieving a similar output using VBA...and mention VBA in the post title, and link to this thread in the body of your message (click on the line-node icon in the upper right of the most appropriate post number to get the URL for it). The reasons?...a new post is more likely to draw fresh eyes upon it, and by clarifying that you want help with VBA, you'll have a better chance getting expertise in that area. Additionally, your original post included a lot of formulas and a Formulas worksheet that have been eliminated, and the emphasis on looking at States has been resolved...both of which could cause confusion if someone tries to work through this thread.

So I think a fresh post would be appropriate, but be sure to include the necessary minisheets to create a working example. In addition to posting a small example of the Planning worksheet displaying the desired results, also include small examples of the TEAM worksheet and ZipCodeDB (I needed to construct a worksheet called ZipCodeDB that looks like the one below so the formulas had access to meaningful data for testing purposes).

MrExcel_20240131_C (version 1).xlsx
	B					G	H
1	ZIP					Lat	Long
2	02809					41.6825	-71.2676
3	02816					41.6914	-71.5768
4	02861					41.8814	-71.356
5	06482					41.4087	-73.2485
6	10017					40.7517	-73.9707
7	11747					40.7946	-73.403
8	19440					40.2778	-75.2975
9	35022					33.3224	-86.9657
10	73108					35.4445	-97.5619
11	87109					35.1506	-106.569
12	90712					33.8512	-118.1457
13	91001					34.1912	-118.1392
14	91402					34.2262	-118.447
15	91714					34.0197	-117.9587
16	91932					32.5783	-117.1148
17	92364					35.4667	-115.2722
18	92606					33.6951	-117.8224
19	92692					33.6144	-117.6433
20	92817					33.8512	-117.7915
21	94507					37.8537	-122.0229
ZipCodeDB

Lastly, I wanted to explain how you can use the formula I offered to dig in further to understand what some of the intermediate results look like. Below, I've dragged the formula (that spills results in the Planning table) to a cell below the table to give more room for spilling results both down and to the right. Then I copied the interior of the last results formula (res) and replaced the final instruction to display "res" with that inner formula. This is the inner array of results that are processed by the "res" formula to eliminate duplicates, sort the results, and take the shortest 5 distances. You'll see there are a number of blanks (1st column) for distance because my version of ZipCodeDB did not contain ZIP codes for those team members, otherwise, the output follows the order of your TEAM worksheet. A more complete ZIPCodeDB that includes those members would be useful, or alternatively, trim down the list of team members to include only those whose ZIP code is present in the ZIPCode DB worksheet (this might be better to keep the example small). In any case, after interrogating a formula using this type of approach, you can either edit the formula to replace the last instruction with "res" and return it to its place in your worksheet, or delete the formula altogether and copy/paste from a neighboring row in the results table. In the snippet below, the bottom of the example Planning table and then below it, the formula interrogation just described. I mention this because when explaining what you want to do, it might be useful to present an example of what the code should do, and ultimately it would be an array resembling what is shown below, before extracting the five shortest distances (so this gives you the ability to copy meaningful intermediate results for posting):

MrExcel_20240131_C (version 1).xlsx
	I	J	K	L	M
15	Customer	1850	VINITA	JONES	92692
16	Customer	1031	VINITA	JONES	92692
17	Customer	1295	LARRY	SMITH	19440
18
19			STEVEN	JONES	85283
20			ROBERT	SMITH	85085
21		3999	THOMAS	APPLE	91402
22		3960	VINITA	JONES	92692
23		4146	SHANE	SMITH	94507
24			THERESA	JONES	80015
25			CASEY	SMITH	80550
26		210	BOB	SMITH	2861
27		70	THOMAS	SMITH	6482
28		204	DAVID	SMITH	2809
29			BILL	SMITH	33019
30			JOB	SMITH	46311
31			BYRON	SMITH	60563
32			STEVE	JONES	60610
33			JOB	SMITH	46311
34			MARCUS	SMITH	41091
35		210	BOB	SMITH	2861
36		204	DAVID	SMITH	2809
37		210	BOB	SMITH	2861
38			JOEL	SMITH	48323
39			JOB	SMITH	46311
40			LOUIE	SMITH	64118
41			JAMES	SMITH	28031
42		210	BOB	SMITH	2861
43		170	LARRY	SMITH	19440
44			JEFFREY	SMITH	89431
45		48	ANDREW	SMITH	10017
46		70	THOMAS	SMITH	6482
47		170	LARRY	SMITH	19440
48		204	DAVID	SMITH	2809
49		210	BOB	SMITH	2861
50			ANDRE	SMITH	45420
51			JOEL	SMITH	48323
52		170	LARRY	SMITH	19440
53			DAVID	SMITH	19006
54			ERIC	SMITH	19341
55			BRIAN	SMITH	19341
56		210	BOB	SMITH	2861
57		204	DAVID	SMITH	2809
58			BRANDON	SMITH	77382
59			0	0	0
60			0	0	0
Planning

Cell Formulas
Range	Formula
I15:I17	I15	=IF($G15<$AF$1,$L15,$AH$1)
J15:AC17	J15	=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, team,TEAM!$B$2:$D$80, basezip,F15, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,CHOOSECOLS(team,1), fname,CHOOSECOLS(team,2), lname,CHOOSECOLS(team,3), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ROUND(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,0),""), res,IF(OR(basezip=0,ISERROR(baselat)),EXPAND("",,20,""),TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5))), res )
J19:M97	J19	=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, team,TEAM!$B$2:$D$80, basezip,F2, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,CHOOSECOLS(team,1), fname,CHOOSECOLS(team,2), lname,CHOOSECOLS(team,3), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ROUND(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,0),""), res,IF(OR(basezip=0,ISERROR(baselat)),EXPAND("",,20,""),TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5))), HSTACK(dist,fname,lname,zip) )
Dynamic array formulas.

 

[pawcoyote]

I would recommend beginning a new post and mention in it that you previously worked through the forum to develop a formula-based solution to this problem, but now you'd like some help achieving a similar output using VBA...and mention VBA in the post title, and link to this thread in the body of your message (click on the line-node icon in the upper right of the most appropriate post number to get the URL for it). The reasons?...a new post is more likely to draw fresh eyes upon it, and by clarifying that you want help with VBA, you'll have a better chance getting expertise in that area. Additionally, your original post included a lot of formulas and a Formulas worksheet that have been eliminated, and the emphasis on looking at States has been resolved...both of which could cause confusion if someone tries to work through this thread.

So I think a fresh post would be appropriate, but be sure to include the necessary minisheets to create a working example. In addition to posting a small example of the Planning worksheet displaying the desired results, also include small examples of the TEAM worksheet and ZipCodeDB (I needed to construct a worksheet called ZipCodeDB that looks like the one below so the formulas had access to meaningful data for testing purposes).

MrExcel_20240131_C (version 1).xlsx
	B					G	H
1	ZIP					Lat	Long
2	02809					41.6825	-71.2676
3	02816					41.6914	-71.5768
4	02861					41.8814	-71.356
5	06482					41.4087	-73.2485
6	10017					40.7517	-73.9707
7	11747					40.7946	-73.403
8	19440					40.2778	-75.2975
9	35022					33.3224	-86.9657
10	73108					35.4445	-97.5619
11	87109					35.1506	-106.569
12	90712					33.8512	-118.1457
13	91001					34.1912	-118.1392
14	91402					34.2262	-118.447
15	91714					34.0197	-117.9587
16	91932					32.5783	-117.1148
17	92364					35.4667	-115.2722
18	92606					33.6951	-117.8224
19	92692					33.6144	-117.6433
20	92817					33.8512	-117.7915
21	94507					37.8537	-122.0229
ZipCodeDB

Lastly, I wanted to explain how you can use the formula I offered to dig in further to understand what some of the intermediate results look like. Below, I've dragged the formula (that spills results in the Planning table) to a cell below the table to give more room for spilling results both down and to the right. Then I copied the interior of the last results formula (res) and replaced the final instruction to display "res" with that inner formula. This is the inner array of results that are processed by the "res" formula to eliminate duplicates, sort the results, and take the shortest 5 distances. You'll see there are a number of blanks (1st column) for distance because my version of ZipCodeDB did not contain ZIP codes for those team members, otherwise, the output follows the order of your TEAM worksheet. A more complete ZIPCodeDB that includes those members would be useful, or alternatively, trim down the list of team members to include only those whose ZIP code is present in the ZIPCode DB worksheet (this might be better to keep the example small). In any case, after interrogating a formula using this type of approach, you can either edit the formula to replace the last instruction with "res" and return it to its place in your worksheet, or delete the formula altogether and copy/paste from a neighboring row in the results table. In the snippet below, the bottom of the example Planning table and then below it, the formula interrogation just described. I mention this because when explaining what you want to do, it might be useful to present an example of what the code should do, and ultimately it would be an array resembling what is shown below, before extracting the five shortest distances (so this gives you the ability to copy meaningful intermediate results for posting):

MrExcel_20240131_C (version 1).xlsx
	I	J	K	L	M
15	Customer	1850	VINITA	JONES	92692
16	Customer	1031	VINITA	JONES	92692
17	Customer	1295	LARRY	SMITH	19440
18
19			STEVEN	JONES	85283
20			ROBERT	SMITH	85085
21		3999	THOMAS	APPLE	91402
22		3960	VINITA	JONES	92692
23		4146	SHANE	SMITH	94507
24			THERESA	JONES	80015
25			CASEY	SMITH	80550
26		210	BOB	SMITH	2861
27		70	THOMAS	SMITH	6482
28		204	DAVID	SMITH	2809
29			BILL	SMITH	33019
30			JOB	SMITH	46311
31			BYRON	SMITH	60563
32			STEVE	JONES	60610
33			JOB	SMITH	46311
34			MARCUS	SMITH	41091
35		210	BOB	SMITH	2861
36		204	DAVID	SMITH	2809
37		210	BOB	SMITH	2861
38			JOEL	SMITH	48323
39			JOB	SMITH	46311
40			LOUIE	SMITH	64118
41			JAMES	SMITH	28031
42		210	BOB	SMITH	2861
43		170	LARRY	SMITH	19440
44			JEFFREY	SMITH	89431
45		48	ANDREW	SMITH	10017
46		70	THOMAS	SMITH	6482
47		170	LARRY	SMITH	19440
48		204	DAVID	SMITH	2809
49		210	BOB	SMITH	2861
50			ANDRE	SMITH	45420
51			JOEL	SMITH	48323
52		170	LARRY	SMITH	19440
53			DAVID	SMITH	19006
54			ERIC	SMITH	19341
55			BRIAN	SMITH	19341
56		210	BOB	SMITH	2861
57		204	DAVID	SMITH	2809
58			BRANDON	SMITH	77382
59			0	0	0
60			0	0	0
Planning

Cell Formulas
Range	Formula
I15:I17	I15	=IF($G15<$AF$1,$L15,$AH$1)
J15:AC17	J15	=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, team,TEAM!$B$2:$D$80, basezip,F15, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,CHOOSECOLS(team,1), fname,CHOOSECOLS(team,2), lname,CHOOSECOLS(team,3), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ROUND(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,0),""), res,IF(OR(basezip=0,ISERROR(baselat)),EXPAND("",,20,""),TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5))), res )
J19:M97	J19	=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, team,TEAM!$B$2:$D$80, basezip,F2, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,CHOOSECOLS(team,1), fname,CHOOSECOLS(team,2), lname,CHOOSECOLS(team,3), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ROUND(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,0),""), res,IF(OR(basezip=0,ISERROR(baselat)),EXPAND("",,20,""),TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5))), HSTACK(dist,fname,lname,zip) )
Dynamic array formulas.

Thank you very much. I will be sure to be more clearer in what I want to do and what I am expecting. I will take my time before posting it to be sure it is clear and concise.