I extracted the abbreviated version and might have not assigned the 'blocks' correctly.ĭepending upon the number of ZIP codes to be looked up and how critical the 'State' information is further checking may be advisable.For example, there is no 530 but the above approach would nevertheless return NY. Not all ZIP codes in the range 501 to 99950 (both inclusive) are valid and the above approach may assign a 'State' to an invalid code.Each 'block' (even if only a single ZIP code) for each 'State' has to be a separate entry since it is the lower limit of each 'block' that is being used to represent each entire 'block'.
There are more entries than 'States' because, although ZIP codes are mostly assigned in blocks, there are inevitably exceptions, for example NY is mostly 10001 to 14925 but also 501 and 544 (Holtsville) and 6390 (Fishers Island). Since the list is sorted ascending a search for 901 will return PR (an exact match) but so will 1000. Give us a call at 1-80 for all of your logistics needs. range_lookup is not included so the default applies and approximate matches are found as well as exact ones. Zip Express’s customer support center is open 24/7 365 days per year including all holidays, meeting your critical logistics needs, no matter the time, day, or weather conditions. Normally not at all a good idea, the fourth parameter for VLOOKUP i.e. Based on the first of the links kindly provided by Bartrup-Cook I think you may not need a table_array of over 42,522 entries (83 may be sufficient) when applying VLOOKUP's inexact matching: Min StateĪssuming the above 2-column array is named ZipState and of workbook scope in the same workbook as the codes you wish to look up and the codes are in a column starting in F1, this formula may serve: =VLOOKUP(F1,ZipState,2)Ĭopied down to suit.
0 kommentar(er)
