How to Lookup Zip Code in Excel [7 Examples]

To lookup the zip code in Excel, follow these steps below:

1. Select cell B10.
2. Take a copy of this formula:=XLOOKUP(A10, E2:E7, B2:B7, “”, 0, 1)
3. To view the outcome, press ENTER.

This XLOOKUP formula searches for the zip code in cell A10 within the range E2:E7. If a match is found, it retrieves the corresponding value from the range B2:B7. If no match is found, it returns an empty cell. The search is set to be an exact match.

Case 1: Excel Zip Code Lookup and Return Single Value

Example 1: Using VLOOKUP Function to Lookup Zip Code

You can use VLOOKUP to Lookup Zip Code and Find anyone’s information from that zip code.

Syntax

=VLOOKUP(lookup_value,tabe_array,col_index_num,range array)

Formula

=VLOOKUP(A10,A2:D7,2,FALSE)

Formula Breakdown

• This formula is designed to search for a specific value in cell A10 within the leftmost column of the range A2 to D7.
• Once a match is found, it retrieves and displays the corresponding value from the second column (column B) of the range.
• The ‘FALSE’ parameter ensures that only exact matches are considered.

It’s similar to telling Excel to search for a specific zip code from a list (A2:D7) and then show relevant information from column B when it is located.

To lookup zip code in Excel, use VLOOKUP function:

1. Select cell B10.
2. Write this formula:=VLOOKUP(A10,A2:D7,2,FALSE)
3. Press the ENTER button.

Final Result

Finally, when I use the VLOOKUP function in Excel to search for a Zip Code in a spreadsheet, it retrieves and returns the corresponding employee name from the same Excel sheet.

Example 2: Using Index Match Functions to Lookup Zip Code in Excel

The INDEX MATCH functions can also be used to look up a zip code in a spreadsheet and return corresponding values from that sheet. In this example, I aim to demonstrate how you can perform a lookup using INDEX MATCH to retrieve an address based on the corresponding zip code.

Syntax

=INDEX(array, MATCH(lookup_value, lookup_array, match_type))

Formula

=INDEX(B2:B7, MATCH(A10, E2:E7, 0))

Formula Breakdown

This formula looks at the value or zip code in cell A10 and searches for an exact match in the range E2 to E7.

• The MATCH function returns the zip code’s location in the range of E2 to E7 after it finds a match. The “0” in MATCH ensures an exact match for accuracy.
• The corresponding result within the range B2 to B7 is then obtained and shown by the INDEX function using this position.

In simpler terms, it’s like telling Excel to find a zip code from one list (E2:E7) and, based on that, show Address related to another list (B2:B7).

To lookup zip code in Excel by using the INDEX MATCH function, follow these steps:

1. Choose cell B10.
2. Copy this formula:=INDEX(B2:B7, MATCH(A10, E2:E7, 0))
3. Hit ENTER to see the result.

Final Result

Ultimately, with the aid of this formula, you can easily retrieve the address from the spreadsheet using the zip code.

Example 3: Using XLOOKUP Function to Lookup Zip Code

The XLOOKUP function is a robust and flexible tool for data lookup. Using XLOOKUP makes searching for a zip code a straightforward process, enabling you to retrieve the corresponding address effortlessly.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Formula

=XLOOKUP(A10, E2:E7, B2:B7, "", 0, 1)

Formula Breakdown

This XLOOKUP function searches for the zip code value in cell A10 within the range E2 to E7.

• It returns the matching value (Address) from the range B2 to B7 when a match has been found.
• The empty quotes (“”) indicate that if there’s no match, it should return an empty cell.
• The “1” at the end of the statement indicates that, if there is an approximate match, it should show the closest match rather than an exact one. The “0” denotes an exact match, guaranteeing accuracy.

In simpler terms, It’s similar to directing Excel to look for a match with a zip code from E2:E7 and then, using that match as a basis, display the Address from another list B2:B7, considering both precise and close matches.

You can use XLOOKUP functions to Lookup zip codes. Follow these steps:

1. Select the cell B10.
2. Take a copy of this formula:=XLOOKUP(A10, E2:E7, B2:B7, “”, 0, 1)
3. To view the outcome, press ENTER.

Final Result

Finally, with the help of this XLOOKUP formula, we can easily retrieve the address associated with the zip code.

Example 4: Using the Combination of INDEX and FILTER Functions to Lookup Zip Code in Excel

Syntax

=INDEX(FILTER(array, include), row_num)

Formula

=INDEX(FILTER(B2:B7, E2:E7=A10), 1)

Formula Breakdown

This formula in Excel is designed to retrieve information based on a specified condition.

• First, values in the range(Address) B2 to B7 are filtered out using the FILTER function based on comparable values in the range(zip code) E2 to E7 that equal the contents of cell A10.
• The INDEX function is used to extract the value from the first (and only) position of the filtered results once the filter finds matched rows. This allows the needed information to be returned.

Essentially, this formula allows you to find and display the first address associated with a specific zip code (A10) from the given data range.

Follow these steps to lookup zip code in Excel by using the combination of INDEX and FILTER functions:

1. Choose on cell B10.
2. Copy this formula:=INDEX(FILTER(B2:B7, E2:E7=A10), 1)
3. Press ENTER to view the outcome.

Final Result

Ultimately, the use of the INDEX and FILTER functions makes the process of looking up a zip code and returning the accurate address a straightforward task.

Example 5: Using the LOOKUP Function to Lookup Zip Code in Excel

Using Excel’s LOOKUP function, one can retrieve results from certain rows and columns by referencing a cell and matching values in that cell to those in another row or column. You can effortlessly find a zip code and relative values by using this LOOKUP function.

Syntax

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Formula

=LOOKUP(2, 1/(E2:E7=A10), B2:B7)

Formula Breakdown

This Excel formula looks for a certain number between E2 and E7 that corresponds to the contents of cell A10.

• Depending on whether every element in E2 through E7 is identical to A10, the equation 1/(E2:E7=A10) generates an array of 1s and errors. Divided by 1, TRUE is effectively converted to 1 and FALSE to an error.
•  The LOOKUP function then searches for the value 2 in this modified array and returns the corresponding value from the range B2 to B7.

You may use this method to locate and extract the number from column B that corresponds to the zip code match (A10) that occurs first in the range of E2 to E7.

You can lookup zip codes in Excel using the LOOKUP function. Here’s how:

1. Select cell B10.
2. Write this formula:=LOOKUP(2, 1/(E2:E7=A10), B2:B7)
3. Click Enter to see the result.

Final Result

At last, we’ve achieved the anticipated outcome.

Case 2: Excel Zip Code Lookup and Return Multiple Values

Example 1: Using the FILTER Function to Lookup Zip Code and Return Multiple Values in Excel

The Excel FILTER function easily applies specific filters to a range of data. This versatile function allows you to look up a zip code, apply filters as needed, and retrieve multiple corresponding values associated with that zip code.

Syntax

=FILTER(array,include,[if_empty])

Formula

=FILTER(A2:D7,E2:E7=A10," ")

Formula Breakdown

This Excel formula is designed to filter a range of data (A2 to D7) based on a specified condition.

• Here, it selects the rows in which the contents of column A10 are equal to the values in the range E2 to E7. The resulting array includes only the rows where this condition is met.
• The empty double quotes (“”) in the third argument act as a placeholder, signaling that the filter does not have any further conditions.

Essentially, this formula helps extract a subset of data where the zip code in column E matches the value in A10, providing a filtered table as a result.

If you want to lookup zip code in Excel and return multiple values, you can use the FILTER function. Here are the steps to follow:

1. Pick cell B10.
2. Type this formula:=LOOKUP(2, 1/(E2:E7=A10), B2:B7)
3. Press Enter to see the result.

Final Result

Applying this formula allows me to easily look up information such as “Name,” “Address,” “County,” and “State” based on a given zip code.

Example 2: Using the Combination of TEXTJOIN and FILTER Functions to Lookup Zip Code and Return Multiple Values in Excel

The TEXTJOIN and FILTER functions combine to perform a zip code lookup, producing multiple outputs in a single cell separated by commas.

Syntax

=TEXTJOIN(delimiter, ignore_empty, FILTER(array, include))

Formula

=TEXTJOIN(", ", TRUE, FILTER(A2:D7, E2:E7=A10))

Formula Breakdown

• To disregard any empty cells, this formula uses the TEXTJOIN function with the parameters “, ” (comma and space) as the delimiter.
• The FILTER function extracts values from A2:D7 according to the E2:E7 condition that the value in cell A10 matches.

This combination of functions essentially concatenates the filtered values into a single text string, separated by commas and with empty cells omitted, creating a consolidated output in a single cell.

To lookup zip code in Excel and return multiple values, using the combination of TEXTJOIN and FILTER functions:

1. Choose cell B10.
2. Type this formula:=TEXTJOIN(“, “, TRUE, FILTER(A2:D7, E2:E7=A10))
3. Press Enter to see the result.

Final Result

At last, the intended result has been located.

Conclusion

In this article, I explore two scenarios. Firstly, I demonstrate the process of retrieving a specific value, such as a name or address, through a zip code lookup. Secondly, I illustrate how you can extract multiple values using a zip code lookup. By following these seven examples, you can seamlessly conduct zip code lookups and effortlessly obtain your desired output.

Frequently Asked Questions

Can Excel pull zip code from the address?

Yes, Excel can extract a zip code from an address. To adeptly extract ZIP codes in Excel, leverage the MID and SEARCH functions with the following steps:

1. Create a new column adjacent to the address column.
2. In the new column’s first cell, use the formula =MID(cell, SEARCH(“Zip”, cell), 5). Adjust as needed based on the structure of your data.
3. Copy the formula down the column to apply it uniformly.
4. Utilize Excel’s filtering or sorting features for insightful data analysis based on the extracted ZIP codes.

Is the zip code and PIN code the same?

In many countries, people use the terms “zip code” and “PIN code” to refer to similar concepts, but they apply them in different regions.

Zip Code:

1. “ZIP” stands for “Zone Improvement Plan.”
2. Used primarily in the United States.
3. Consists of five numerical digits, with an optional additional four digits for more precise location information (ZIP+4).

PIN Code:

1. “PIN” stands for “Postal Index Number.”
2. Used in India.
3. Consists of six numerical digits.

Can Excel map ZIP codes?

Excel itself does not offer native mapping capabilities. However, you can map ZIP codes in Excel by obtaining a dataset with geographical information, such as latitude and longitude, importing the data into Excel, and using external tools like Microsoft Power Map, Power BI, or online mapping services such as Google Maps or Mapbox for visualization.