Zipcode list

Question

I am using Excel for Mac (13.3.1), Version 16.72. I have a table of member information (names, addresses, etc.). I would like to search on the zip code field for zips within a 50-mile radius of my location (I have a separate list of 129 zip codes for this purpose) and list the member information on a separate worksheet so I can perform a mail merge. There may be more than one record per zip code. What formula/function can I use to perform the searches and list the results?

Answer 1

Hi Lester

Thank you for sharing the sample file

On the "Table" sheet

1. We converted the table into an Excel table and named it "DataUpdate"

2. Upload both, the "DataUpdate" and "Zip" tables to Power Query
3. Select Merge Queries >>> New Query

4 and 5) Choose the 2 tables as indicated in the picture and select the columns to Match in this case the "Zip" column

6 and 7) Choose the Join Kind as indicated and press OK

8. Give/ Rename the Merged table as you like, (eg. "50_MilesRadio")
9. Click the double arrows to expand the Table and uncheck the fields as in the picture below

10. Filter out the null rows, in order to display only the Zip codes with data in it

And upload the resulting table back to excel

VOILA!!!

You may find the sample file with the PQ solution here https://we.tl/t-Z5Cw5xRkkM

11 and 12) Replace the sample data with your real info and REFRESH the Query

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Excel Help

I tried creating the Power Query and was doing fine until I reached the part where I was to add the zip code table using Data>"From Table/Range", which does not exist in Excel for Mac.

The zip codes within 50 miles of my location are listed in the Zip Codes tab. Ideally, I would like Excel to look at that table and pull in the information automatically as I will be receiving an updated spreadsheet monthly and there are 129 zip codes within 50 miles of my location. Although there will not always be a match to all 129 zip codes, it would be very tedious to have to put in one zip code at a time to pull the information.

On the XLookup tab, I added 3 samples. The sample with the 78070 zip code only listed 1 record when there are actually 3.

Thank you again for your help with this puzzle.

Answer 3

OK

Kindly suggest preparing and uploading a sample file to Onedrive, Dropbox, etc ... and share the link here.

This will help us to give you a prompt and right solution.

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also follow the instructions in this video

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany

Answer 4

Thank you so much for responding. Power Queries seem to be too advanced for my novice self. I utilized an XLOOKUP formula/function, but it only returned the first instance of the zip code input:

=XLOOKUP($B$1,H2:H3921,'C2:H3921)

Answer 5

Hi Lester

IMHO

Power Query might help you.

The videos below will give you some guidance.

Do let us know if you need more help

Regards

Jeovany

https://youtu.be/28T0XzGNZyM

https://youtu.be/45UkGoFMswI