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]

If I am understanding correctly, you do not want to limit the zip codes evaluated to only those within the state represented by whichever row you are on in the Formulas worksheet, yet that is what your formulas are doing. If you want to calculate the distance between the baseline location whose lat and long are in columns C:D on the Formulas sheet, I would recommend a different approach that takes advantage of some other Excel 365 functions. The example below uses a single, multi-part formula to compute the distances between the baseline location and all zip codes, sorts them according to distance, eliminates duplicate rows, and performs the lookups to identify who is associated with each zip...and the results are shown in terms of zip, lat, long, dist, fname, lname. The output array shown is merely an example. It can be trimmed down and restructured as needed, but what specifically do you want to do with these results? Do you want the top 2, 3, 5 displayed in a single row? As an aside, your zip codes need some attention, as Excel drops the leading 0, so some zip codes appear with only 4-digits.

MrExcel_20240131_C (version 1).xlsb
	AG	AH	AI	AJ	AK	AL
2	10017	40.7517	-73.9707	48.09604	ANDREW	SMITH
3	6482	41.4087	-73.2485	69.578899	THOMAS	SMITH
4	19440	40.2778	-75.2975	170.28655	LARRY	SMITH
5	2809	41.6825	-71.2676	204.25017	DAVID	SMITH
6	2861	41.8814	-71.356	209.53438	BOB	SMITH
7	92692	33.6144	-117.6433	3960.0082	VINITA	JONES
8	91402	34.2262	-118.447	3999.4438	THOMAS	APPLE
9	94507	37.8537	-122.0229	4145.8905	SHANE	SMITH
10	85283				STEVEN	JONES
11	85085				ROBERT	SMITH
12	80015				THERESA	JONES
13	80550				CASEY	SMITH
14	33019				BILL	SMITH
15	46311				JOB	SMITH
16	60563				BYRON	SMITH
17	60610				STEVE	JONES
18	41091				MARCUS	SMITH
19	48323				JOEL	SMITH
20	64118				LOUIE	SMITH
21	28031				JAMES	SMITH
22	89431				JEFFREY	SMITH
23	45420				ANDRE	SMITH
24	19006				DAVID	SMITH
25	19341				ERIC	SMITH
26	77382				BRANDON	SMITH
27	0				0	0
Formulas

Cell Formulas
Range	Formula
AG2:AL27	AG2	=LET(baselat,C2,baselong,D2, zips,TEAM!$B$2:$B$80, fname,XLOOKUP(zips,TEAM!$B$2:$B$80,TEAM!$C$2:$C$80,"",0), lname,XLOOKUP(zips,TEAM!$B$2:$B$80,TEAM!$D$2:$D$80,"",0), lats,XLOOKUP(zips, ZipCodeDB!$B$2:$B$49973,ZipCodeDB!$G$2:$G$49973,"",0), longs,XLOOKUP(zips, ZipCodeDB!$B$2:$B$49973,ZipCodeDB!$H$2:$H$49973,"",0), dists,IFERROR(ACOS(COS(RADIANS(90-lats))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lats))*SIN(RADIANS(90-baselat))*COS(RADIANS(longs-baselong)))*6378.135,""), res,SORT(UNIQUE(HSTACK(zips,lats,longs,dists,fname,lname)),4,1), res)
Dynamic array formulas.

 

[KRice]

You might want to consider trimming the list of zip codes that are to be considered for the distance computation by establishing a bounding box, perhaps +/- 5 degrees (pick x) latitude and +/- 7 degrees (pick y) longitude from the baseline "0-distance" location. You could also add to this list any other zip codes that are in the same state as the baseline location, even if the lat/long of those zips lies outside the bounding box. This would greatly reduce the number of distance computations needed and still find the shortest distance locations.

As I reviewed the formula I posted, I notice some additional adjustments that would shorten it further...particularly with regard to how the zips, fname, and lname arrays are formed, but those changes might be better made as part of refining the output.

 

[pawcoyote]

Hi, thank you for the response. I will be giving this more thought for you. I should have a response to you tomorrow.

 

[pawcoyote]

You might want to consider trimming the list of zip codes that are to be considered for the distance computation by establishing a bounding box, perhaps +/- 5 degrees (pick x) latitude and +/- 7 degrees (pick y) longitude from the baseline "0-distance" location. You could also add to this list any other zip codes that are in the same state as the baseline location, even if the lat/long of those zips lies outside the bounding box. This would greatly reduce the number of distance computations needed and still find the shortest distance locations.

As I reviewed the formula I posted, I notice some additional adjustments that would shorten it further...particularly with regard to how the zips, fname, and lname arrays are formed, but those changes might be better made as part of refining the output.

Hi, what I am trying to do is have the shortest distances shown in Ref1 thru Ref5 from the final zip code (lat/long) to one of the zip codes on the Team spreadsheet and put them in order from shortest distance to longest. Right now, it looks at the State first then the zip code what I would also like to do is have it look at only the Zip Code and not have anything to do with state. So, formula spreadsheet looks at team spreadsheet but starts looking at the zip code vs the state then zip code. I hope this makes a bit more sense. With regards to the leading zeros on the zips I us format the column to special zip code. Any guidance is appreciated. If this cannot be done, I totally understand. I have actually updated the code for O2, S2, W2, AA2, and AE2. I changed from Kilometers to Miles.

 =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)))*AVERAGE(3959,3961)))

 

[KRice]

Right now, it looks at the State first then the zip code what I would also like to do is have it look at only the Zip Code and not have anything to do with state.

This is what my question was getting at. Given some baseline location whose lat/long are shown in column B of the Formulas worksheet, you want to determine which are the five closest zip codes that appear in the Team worksheet. And the ZipCodeDB worksheet is needed as a cross reference to provide lat/long for any zip codes in the Team worksheet. Is that correct? That is what the formula I offered earlier does. Have a look at the results returned. If that is what you want, then the output of that array can be adjusted to deliver just the content desired. You don't need the Formulas worksheet at all, as the results can be obtained using the input zip code in column F of the Planning worksheet, and then after developing the results that depend on the ZipCodeDB and TEAM worksheets, the final results can be spilled across a row in the Planning worksheet, as shown in the slightly reworked version below. Then some other formulas in the columns G:J range may not be needed (note that the min formula in column G isn't referencing the correct cells right now).

MrExcel_20240131_C (version 1).xlsx
	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	Address	CITY	STATE	ZIP CODE	Min - Dist	Min Zip	Distance	Ricoh / Client	Distance	First Name	Last Name	Zip	Distance	First Name	Last Name	Zip	Distance	First Name	Last Name	Zip	Distance	First Name	Last Name	Zip	Distance	First Name	Last Name	Zip
2	123 Smith Street	MELVILLE	NY	11747	69.578899	6482	69.578899	Customer	48.09604	ANDREW	SMITH	10017	69.578899	THOMAS	SMITH	6482	170.28655	LARRY	SMITH	19440	204.25017	DAVID	SMITH	2809	209.53438	BOB	SMITH	2861
Planning

Cell Formulas
Range	Formula
G2	G2	=MIN(Formulas!O2,Formulas!S2,Formulas!W2,Formulas!AA2,Formulas!AE2)
H2	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	I2	=IF(G2<5000,G2,"")
J2	J2	=IF($I2<$AG$1,$M2,$AI$1)
K2:AD2	K2	=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, basezip,F2, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,TEAM!$B$2:$B$80, fname,XLOOKUP(zip,TEAM!$B$2:$B$80,TEAM!$C$2:$C$80), lname,XLOOKUP(zip,TEAM!$B$2:$B$80,TEAM!$D$2:$D$80), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,""), res,TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5)), res)
Dynamic array formulas.

 

[pawcoyote]

Roger that and I will make your recommendations and see how it goes. That would be awesome if I didn't need the formula sheet. Roger on the min formula as well. Thank you!

 

[pawcoyote]

@KRice sorry I have been ill and not able to work on this at the moment. I hope to get to it next week. Thank you for any guidance you are providing.

 

[KRice]

Not a problem...get well soon! When you are feeling better, have a look at the K2 formula in my last mini sheet post to see if the results make sense and are delivered in the format desired.

 

[pawcoyote]

Not a problem...get well soon! When you are feeling better, have a look at the K2 formula in my last mini sheet post to see if the results make sense and are delivered in the format desired.

Finally feeling a bit better. Came down with Covid and it knocked me out. Will be looking at everything and ask questions if that is ok. Thank you for your patience.