Spreadsheet: 772321d1647199153-vlookup-on-a-different-worksheet-and-a-nested-if-function-cities-using-ssti
Sheet: Tier 3 Cities

Current SSTI customer |  |  |  |  |  | [NAME] | Total City Square Miles |  | Contract Size | Price per Sq. Mile | Potential expansion |  | estimate can't find actual
 | 101 | Hialeah | Florida |  |  |  | 21.6 sq mi
 | 102 | San Bernardino | California |  |  |  | 62.1 sq mi
 | 103 | Tacoma | Washington |  |  |  | 49.7 sq mi
 | 104 | Modesto | California |  |  |  | 43.0 sq mi
 | 105 | Huntsville | Alabama |  |  |  | 218.1 sq mi
 | 106 | Des Moines | Iowa |  |  |  | 88.2 sq mi
 | 107 | Yonkers | New York |  |  |  | 18.0 sq mi
 | 108 | Rochester | New York |  |  | 7 | 35.8 sq mi |  | 130000
 | 109 | Moreno Valley | California |  |  |  | 51.3 sq mi
 | 110 | Fayetteville | North Carolina |  |  |  | 148.3 sq mi
 | 111 | Fontana | California |  |  |  | 43.1 sq mi
 | 112 | Columbus[u] | Georgia |  |  |  | 216.5 sq mi
 | 113 | Worcester | Massachusetts |  |  | 6 | 37.4 sq mi
 | 114 | Port St. Lucie | Florida |  |  |  | 119.2 sq mi
 | 115 | Little Rock | Arkansas |  |  | 2 | 120.0 sq mi
 | 116 | Augusta[v] | Georgia |  |  |  | 302.3 sq mi
 | 117 | Oxnard | California |  |  |  | 26.5 sq mi
 | 118 | Birmingham | Alabama |  |  | 20 | 147.0 sq mi
 | 119 | Montgomery | Alabama |  |  | 1 | 159.9 sq mi
 | 120 | Frisco | Texas |  |  |  | 68.6 sq mi
 | 121 | Amarillo | Texas |  |  |  | 102.3 sq mi
 | 122 | Salt Lake City | Utah |  |  |  | 110.3 sq mi
 | 123 | Grand Rapids | Michigan |  |  |  | 44.8 sq mi
 | 124 | Huntington Beach | California |  |  |  | 27.0 sq mi
 | 125 | Overland Park | Kansas |  |  |  | 75.2 sq mi
 | 126 | Glendale | California |  |  | 2 | 30.5 sq mi
 | 127 | Tallahassee | Florida |  |  |  | 100.9 sq mi
 | 128 | Grand Prairie | Texas |  |  |  | 72.6 sq mi
 | 129 | [NAME] | Texas |  |  |  | 67.0 sq mi
 | 130 | Cape Coral | Florida |  |  |  | 106.0 sq mi
 | 131 | [NAME] | South Dakota |  |  |  | 79.1 sq mi
No | 132 | Peoria | Arizona |  |  |  | 176.1 sq mi
 | 133 | Providence | Rhode Island |  |  |  | 18.4 sq mi
 | 134 | Vancouver | Washington |  |  |  | 48.7 sq mi
 | 135 | Knoxville | Tennessee |  |  |  | 98.7 sq mi
 | 136 | Akron | Ohio |  |  |  | 61.9 sq mi
 | 137 | Shreveport | Louisiana |  |  |  | 107.8 sq mi
 | 138 | Mobile | Alabama |  |  |  | 139.5 sq mi
 | 139 | Brownsville | Texas |  |  |  | 131.5 sq mi
 | 140 | Newport News[m] | Virginia |  |  |  | 69.0 sq mi
 | 141 | Fort Lauderdale | Florida |  |  | 4 | 34.6 sq mi
 | 142 | Chattanooga | Tennessee |  |  |  | 142.4 sq mi
 | 143 | Tempe | Arizona |  |  |  | 39.9 sq mi
 | 144 | [NAME] | Illinois |  |  |  | 45.0 sq mi
 | 145 | Santa Rosa | California |  |  |  | 42.5 sq mi
 | 146 | [NAME] | Oregon |  |  |  | 44.2 sq mi
 | 147 | [NAME] | California |  |  |  | 42.0 sq mi
 | 148 | Salem | Oregon |  |  |  | 48.8 sq mi
 | 149 | Ontario | California |  |  |  | 50.0 sq mi
 | 150 | Cary[w] | North Carolina |  |  |  | 59.2 sq mi
 | 151 | Rancho Cucamonga | California |  |  |  | 40.1 sq mi
 | 152 | Oceanside | California |  |  |  | 41.3 sq mi
 | 153 | Lancaster | California |  |  |  | 94.3 sq mi |  |  |  |  |  |  | https://www.springfieldmo.gov/AgendaCenter/ViewFile/Item/15756?fileID=213605
 | 154 | Garden Grove | California |  |  |  | 18.0 sq mi |  |  |  |  |  |  | Pricing proposal for Springfield MO in 2/16/21
 | 155 | Pembroke Pines | Florida |  |  |  | 32.7 sq mi
 | 156 | [NAME] | Colorado |  |  |  | 57.2 sq mi
 | 157 | Palmdale | California |  |  |  | 106.1 sq mi
 | 158 | Springfield | Missouri |  |  | 3 | 82.4 sq mi
 | 159 | [NAME] | Tennessee |  |  |  | 99.4 sq mi
 | 160 | Salinas | California |  |  |  | 23.5 sq mi |  | 455000
 | 161 | Hayward | California |  |  |  | 45.8 sq mi
 | 162 | Paterson | New Jersey |  |  | 1 | 8.4 sq mi
 | 163 | Alexandria[m] | Virginia |  |  |  | 14.9 sq mi
 | 164 | Macon[x] | Georgia |  |  |  | 249.4 sq mi
 | 165 | Corona | California |  |  |  | 39.9 sq mi
 | 166 | Kansas City[y] | Kansas |  |  |  | 124.7 sq mi
 | 167 | Lakewood | Colorado |  |  |  | 43.5 sq mi
 | 168 | Springfield | Massachusetts |  |  | 4.25 | 31.9 sq mi
 | 169 | Sunnyvale | California |  |  |  | 22.1 sq mi
 | 170 | [NAME] | Mississippi |  |  | 3 | 111.7 sq mi
 | 171 | [NAME] | Texas |  |  |  | 54.8 sq mi
 | 172 | Hollywood | Florida |  |  |  | 27.3 sq mi
 | 173 | Murfreesboro | Tennessee |  |  |  | 62.9 sq mi
 | 174 | Pasadena | Texas |  |  |  | 43.7 sq mi
 | 175 | Bellevue | Washington |  |  |  | 33.5 sq mi
 | 176 | [NAME] | California |  |  |  | 23.0 sq mi
 | 177 | Escondido | California |  |  |  | 37.3 sq mi
 | 178 | Joliet | Illinois |  |  |  | 64.5 sq mi
 | 179 | Charleston | South Carolina |  |  |  | 114.8 sq mi
 | 180 | Mesquite | Texas |  |  |  | 48.5 sq mi
 | 181 | Naperville | Illinois |  |  |  | 39.1 sq mi
 | 182 | Rockford | Illinois |  |  | 6 | 64.5 sq mi |  | 310000
 | 183 | Bridgeport | Connecticut |  |  | 5.5 | 16.1 sq mi
 | 184 | Syracuse | New York |  |  |  | 25.1 sq mi
 | 185 | Savannah | Georgia |  |  | 6.5 | 106.8 sq mi
 | 186 | Roseville | California |  |  |  | 44.1 sq mi
 | 187 | Torrance | California |  |  |  | 20.5 sq mi
 | 188 | [NAME] | California |  |  |  | 22.4 sq mi
 | 189 | Surprise | Arizona |  |  |  | 110.3 sq mi
 | 190 | [NAME] | Texas |  |  |  | 62.3 sq mi
 | 191 | [NAME] | Colorado |  |  |  | 35.9 sq mi
 | 192 | Visalia | California |  |  |  | 37.9 sq mi
 | 193 | Olathe | Kansas |  |  |  | 61.9 sq mi
 | 194 | Gainesville | Florida |  |  |  | 63.2 sq mi
 | 195 | West Valley City | Utah |  |  |  | 35.8 sq mi
 | 196 | Orange | California |  |  |  | 25.7 sq mi
 | 197 | [NAME] | Texas |  |  |  | 96.4 sq mi
 | 198 | [NAME] | Michigan |  |  |  | 34.4 sq mi
 | 199 | Pasadena | California |  |  |  | 23.0 sq mi
 | 200 | [NAME] | Texas |  |  |  | 88.7 sq mi
 | 201 | Cedar Rapids | Iowa |  |  |  | 72.1 sq mi
 | 202 | Dayton | Ohio |  |  | 3 | 55.8 sq mi
 | 203 | [NAME] | New Jersey |  |  |  | 12.3 sq mi
 | 204 | Hampton[m] | Virginia |  |  |  | 51.5 sq mi
 | 205 | Columbia | South Carolina |  |  | 6 | 136.8 sq mi
 | 206 | Kent | Washington |  |  |  | 33.8 sq mi
 | 207 | Stamford | Connecticut |  |  |  | 37.6 sq mi
 | 208 | Lakewood | New Jersey |  |  |  | 24.7 sq mi |  |  |  |  |  | Miramar, FL only approved $595k over a 3 year period
 | 209 | Victorville | California |  |  |  | 73.7 sq mi |  |  |  |  |  | https://miramar.legistar.com/LegislationDetail.aspx?ID=4982649&GUID=23F99341-AC28-4DF3-B867-E0F0D5B1FB56&Options=&Search=
 | 210 | Miramar | Florida |  |  | 4 | 28.9 sq mi |  |  |  |  |  | https://miramar.legistar.com/View.ashx?M=F&ID=9472459&GUID=0ED75FBB-C201-4931-A569-5846E6FEC4BF
 | 211 | Coral Springs | Florida |  |  |  | 22.9 sq mi
 | 212 | Sterling Heights | Michigan |  |  |  | 36.4 sq mi
 | 213 | New Haven | Connecticut |  |  | 5.5 | 18.7 sq mi
 | 214 | Carrollton | Texas |  |  |  | 36.7 sq mi
 | 215 | Midland | Texas |  |  |  | 75.5 sq mi
 | 216 | [NAME] | Oklahoma |  |  |  | 178.8 sq mi
 | 217 | Santa Clara | California |  |  |  | 18.3 sq mi
 | 218 | Athens[z] | Georgia |  |  |  | 116.3 sq mi
 | 219 | Thousand Oaks | California |  |  |  | 55.3 sq mi
 | 220 | Topeka | Kansas |  |  |  | 61.4 sq mi
 | 221 | Simi Valley | California |  |  |  | 41.5 sq mi
 | 222 | Columbia | Missouri |  |  | 1 | 66.5 sq mi
 | 223 | Vallejo | California |  |  |  | 30.4 sq mi
 | 224 | Fargo | North Dakota |  |  |  | 49.8 sq mi
 | 225 | Allentown | Pennsylvania |  |  |  | 17.6 sq mi
 | 226 | Pearland | Texas |  |  |  | 48.7 sq mi
 | 227 | Concord | California |  |  |  | 30.6 sq mi
 | 228 | Abilene | Texas |  |  |  | 106.7 sq mi
 | 229 | Arvada | Colorado |  |  |  | 38.9 sq mi
 | 230 | Berkeley | California |  |  |  | 10.4 sq mi
 | 231 | [NAME] | Michigan |  |  |  | 28.2 sq mi
 | 232 | Independence | Missouri |  |  |  | 78.0 sq mi
 | 233 | Rochester | Minnesota |  |  | 1 | 55.5 sq mi
 | 234 | Lafayette[aa] | Louisiana |  |  |  | 55.8 sq mi
 | 235 | Hartford | Connecticut |  |  | 11.25 | 17.4 sq mi |  | 500000
 | 236 | College Station | Texas |  |  |  | 51.2 sq mi
 | 237 | Clovis | California |  |  |  | 25.4 sq mi
 | 238 | Fairfield | California |  |  |  | 41.6 sq mi
 | 239 | Palm Bay | Florida |  |  |  | 86.4 sq mi
 | 240 | Richardson | Texas |  |  |  | 28.6 sq mi
 | 241 | Round Rock | Texas |  |  |  | 37.6 sq mi
 | 242 | Cambridge | Massachusetts |  |  | 1.25 | 6.4 sq mi
 | 243 | Meridian | Idaho |  |  |  | 35.1 sq mi
 | 244 | West Palm Beach | Florida |  |  | 4.6 | 53.8 sq mi
 | 245 | Evansville | Indiana |  |  |  | 47.4 sq mi
 | 246 | Clearwater | Florida |  |  |  | 26.1 sq mi
 | 247 | Billings | Montana |  |  |  | 44.8 sq mi
 | 248 | West Jordan | Utah |  |  |  | 32.3 sq mi
 | 249 | Richmond | California |  |  | 6 | 30.1 sq mi
 | 250 | Westminster | Colorado |  |  |  | 31.6 sq mi
 | 251 | [NAME] | New Hampshire |  |  |  | 33.1 sq mi
 | 252 | Lowell | Massachusetts |  |  |  | 13.6 sq mi
 | 253 | [NAME] | North Carolina |  |  | 6 | 51.4 sq mi
 | 254 | Antioch | California |  |  |  | 29.2 sq mi
 | 255 | Beaumont | Texas |  |  |  | 82.5 sq mi
 | 256 | Provo | Utah |  |  |  | 41.7 sq mi
 | 257 | North Charleston | South Carolina |  |  |  | 77.6 sq mi
 | 258 | Elgin | Illinois |  |  |  | 38.0 sq mi
 | 259 | Carlsbad | California |  |  |  | 37.8 sq mi
 | 260 | Odessa | Texas |  |  |  | 51.1 sq mi
 | 261 | Waterbury | Connecticut |  |  |  | 28.5 sq mi
 | 262 | Springfield | Illinois |  |  | 4.25 | 61.1 sq mi
 | 263 | League City | Texas |  |  |  | 51.3 sq mi
 | 264 | Downey | California |  |  |  | 12.4 sq mi
 | 265 | [NAME] | Oregon |  |  |  | 23.5 sq mi
 | 266 | High Point | North Carolina |  |  |  | 56.4 sq mi
 | 267 | [NAME] | Oklahoma |  |  |  | 63.0 sq mi
 | 268 | Peoria | Illinois |  |  | 3 | 48.0 sq mi
 | 269 | Lansing | Michigan |  |  |  | 39.1 sq mi
 | 270 | Lakeland | Florida |  |  |  | 66.2 sq mi
 | 271 | Pompano Beach | Florida |  |  | 5 | 24.0 sq mi
 | 272 | Costa Mesa | California |  |  |  | 15.8 sq mi
 | 273 | Pueblo | Colorado |  |  |  | 55.4 sq mi
 | 274 | Lewisville | Texas |  |  |  | 37.0 sq mi
 | 275 | Miami Gardens | Florida |  |  | 4.5 | 18.2 sq mi
 | 276 | Las Cruces | New Mexico |  |  |  | 76.9 sq mi
 | 277 | Sugar Land | Texas |  |  |  | 40.5 sq mi
 | 278 | Murrieta | California |  |  |  | 33.6 sq mi
 | 279 | Ventura[ab] | California |  |  |  | 21.9 sq mi
 | 280 | [NAME] | Washington |  |  | 1 | 33.2 sq mi
 | 281 | [NAME] | California |  |  |  | 37.3 sq mi
 | 282 | Dearborn | Michigan |  |  |  | 24.2 sq mi
 | 283 | Santa Maria | California |  |  |  | 22.8 sq mi
 | 284 | West Covina | California |  |  |  | 16.0 sq mi
 | 285 | El Monte | California |  |  |  | 9.6 sq mi
 | 286 | [NAME] | Colorado |  |  |  | 48.9 sq mi
 | 287 | Sparks | Nevada |  |  |  | 36.4 sq mi
 | 288 | Centennial | Colorado |  |  |  | 29.7 sq mi
 | 289 | Boulder | Colorado |  |  |  | 26.3 sq mi
 | 290 | [NAME] | Georgia |  |  |  | 37.7 sq mi
 | 291 | Inglewood | California |  |  |  | 9.1 sq mi
 | 292 | Edison | New Jersey |  |  |  | 30.1 sq mi
 | 293 | South Fulton | Georgia |  |  |  | 85.2 sq mi
 | 294 | Green Bay | Wisconsin |  |  |  | 45.5 sq mi
 | 295 | Burbank | California |  |  |  | 17.3 sq mi
 | 296 | [NAME] | Washington |  |  |  | 23.5 sq mi
 | 297 | Hillsboro | Oregon |  |  |  | 25.7 sq mi
 | 298 | El Cajon | California |  |  |  | 14.5 sq mi
 | 299 | Tyler | Texas |  |  |  | 57.5 sq mi
 | 300 | Davie[ad] | Florida |  |  |  | 34.9 sq mi
 | 301 | San Mateo | California |  |  |  | 12.1 sq mi
 | 302 | Brockton | Massachusetts |  |  | 5 | 21.3 sq mi
 | 303 | Concord | North Carolina |  |  |  | 63.5 sq mi
 | 304 | Jurupa Valley[ae] | California |  |  |  | 42.9 sq mi
 | 305 | Daly City | California |  |  |  | 7.6 sq mi
 | 306 | [NAME] | Texas |  |  |  | 26.4 sq mi
 | 307 | Rio Rancho | New Mexico |  |  |  | 103.4 sq mi
 | 308 | Rialto | California |  |  |  | 24.1 sq mi
 | 309 | [NAME] | New Jersey |  |  |  | 23.3 sq mi
 | 310 | South Bend | Indiana |  |  | 4 | 42.0 sq mi
 | 311 | Spokane Valley | Washington |  |  |  | 37.7 sq mi
 | 312 | Norwalk | California |  |  |  | 9.7 sq mi
 | 313 | Menifee | California |  |  |  | 46.5 sq mi
 | 314 | Vacaville | California |  |  |  | 29.9 sq mi
 | 315 | [NAME] | Texas |  |  |  | 72.0 sq mi
 | 316 | Davenport | Iowa |  |  |  | 63.8 sq mi
 | 317 | Quincy | Massachusetts |  |  |  | 16.6 sq mi
 | 318 | Chico | California |  |  |  | 34.1 sq mi
 | 319 | [NAME] | Massachusetts |  |  |  | 10.7 sq mi
 | 320 | [NAME]'s Summit | Missouri |  |  |  | 63.9 sq mi
 | 321 | New Bedford | Massachusetts |  |  |  | 20.0 sq mi |  | 157000
 | 322 | Federal Way | Washington |  |  |  | 22.3 sq mi
 | 323 | [NAME] | Michigan |  |  |  | 28.1 sq mi
 | 324 | Edinburg | Texas |  |  |  | 44.7 sq mi
 | 325 | Nampa | Idaho |  |  |  | 33.5 sq mi
 | 326 | Roanoke[m] | Virginia |  |  |  | 42.5 sq mi
 |  |  |  |  | Total
 |  |  |  |  | [NAME] | Cities |  | Total Opportunity
 |  |  |  | Tier 3 Opportunity | 4 |  | 50000
 |  |  |  | Tier 3 Expansion