Need to format two groups of zip codes so they work for exact matching

Matching/IF()/lookup functions should not be affected by number formatting. Functions use the value stored in the cell and don't look at the number formatting.

I would guess that you have non-printing characters in some of what you are pasting from the web. If I were to debug this, I would probably start by pasting a sampling of zip codes into Excel, then use something like UNICODE(MID(zip code text,1...Len(zip code text),1) [pseudo-formula, not syntactically correct, so you can't just paste this into Excel] and see what characters are being pasted into Excel.

Once I know what characters are being pasted into Excel, then I would identify the actual zip-code characters and the extraneous characters. Then figure out a way to extract just the zip-code characters and get rid of the garbage characters. Note that CLEAN() and TRIM() only work on some characters (mostly the basic ASCII character list) and do not clean out higher coded characters (like the space 160 character).

Once I have figured out how to clean off all of the garbage characters, then you will need to decide how you want to store you zip codes. If you are working with basic 5 digit US zip codes, then i would probably store them as 5 digit numbers (avoid storing numbers stored as text). If you have 5 + 4 digit US zip codes, then store as 9 digit numbers (formatted as 00000-0000). If you have a mix of 5 digit zip codes and 5+4, then you will need to think about how to handle those differences. If you want to extend this to international postal codes (that can use both alphabetic and numeric characters in different combinations), then you may want to store all of the zip codes as text and always search using "numbers stored as text."

That's what I would do. I think it's important to start with a careful analysis of the text you are pasting into Excel so you can get a reliable method of cleaning the text of any of the garbage characters that are often a part of web data pasted into Excel. Once you have a reliable method of cleaning the text data, I expect the rest of the project will be relatively straightforward.