Lookup city/state combo (Excel 2003 (all))

Hi all,
I need assistance in writing a formula to generate a count (of matches) between two tables (on different sheets). Each table has two cols; City and State. Both tables are sorted alphabetically by city then state, and duplicates are removed. I want to test if any given city/state pair (in the shorter master table) exist in the second Data table.

So in this case
I tried VLOOKUP but can't get it to work to compare both city/state at same time (since cities alone are not unique).

The end goal is to determine what the % of the 'master' list entries exist in the 'data' list (do 33% match or 27% or ??). On 2nd thought it's really the intersection of the two lists (what city/state pairs they have in common).

I attached a subset of the cities/states (just the "A" cities) for you to play with.

Re: Lookup city/state combo (Excel 2003 (all))

Deb,

Just a quick attempt... the gurus will probably come up with something better. Created two named ranges on the A-Cities sheet. On the Master sheet entered the formula =SUM((CityRange=A2)*(StateRange=B2)).

Re: Lookup city/state combo (Excel 2003 (all))

These lists are quite big (and just step 1 in several steps I need to do) and it's amazing how much 'bad' data there was. I had to look through each sheet manually to make sure that things like "Ft. Lauderdale" isn't spelled as "Fort Lauderdale" in one list but the other. Obviously when the data was first entered they weren't consistent with the name formats. It definitely matters when you're comparing stuff.