Find a list of values within a table, locate & extract matching rows

I'm preparing a large data set of addresses for use in ArcGIS.

I need all addresses in the final list to match one of the valid CA zip codes from my list of valid zip codes.

How can I search for ALL of these zip codes within my data set & identify all rows with matching zips?

I would like to do this and remove any rows with zips that do not match the list.



FYI i have been doing this in a weird roundabout manual way:
Pasting the list of valid zips to the end of the zip code column in my data set, then sorting for duplicates and removing all that are not duplicates.

None of the sample data you provided match with a mailing city in the ZIPS list ...

Please provide properly representative data (not real names, but mailing cities need to be correct) that contain some matches and some non-matches, preferably all in one workbook rather than split over two. Thanks.

I see, I'm sorry about that!

I attached one new sample set with two sheets. There should be some matching & non matching zips & city names now.

Let me know if you have any other questions. Thank you so much!

Mmm ... Well, now I have no idea what you want. What results are you expect in for the sample set? Fill these manually abs post again.

Where are the expected results? What does find and select look like?

Sorry, but I didn’t need you to simplify that dataset, I just needed you to manually add your expected results and mark them out clearly so I can see what you are looking for. I cannot see where you have done this.

As modeled on the Main Sample Set sheet the following conditional formatting formula is applied to C2:C24

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Change the rule to this:

=ISNUMBER(MATCH(VALUE(C2),ZipCodeSampleSet!A$2:A$22,0))

Those 'numbers' with a green triangle in column C are not numbers, but text. The addition of the VALUE function will overcome this anomaly in your data.