# Thread: Lookup city/state combo (Excel 2003 (all))

1. ## 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.

<pre>Master table: Sheet1 (2133 entries)
City | State
Abbeville AL
Abbeville LA
Abbeville SC
Abberdeen MS
Abbertden NC
.etc.</pre>

<pre>Data table: Sheet2 (6847entries)
City | State
city1 XX
Abbeville SC
city2 XX
Abbertden NC</pre>

So my output would be (listed on sheet with Master table):<pre>Master Table (column A | B | C)
Abbeville SC 1 (or True or Yes, or 'x' or whatever)
Abbertden NC 1 (or True or Yes, or 'x' whatever)</pre>

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.

Thanks much, <img src=/S/help.gif border=0 alt=help width=23 height=15> <img src=/S/please.gif border=0 alt=please width=31 height=23>
Deb

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

If you put the formula:

<pre>=A1&B1
</pre>

in cell C1 on Sheet2 and copy it down to C6847, then you could put this formula:

<pre>=NOT(ISERROR(MATCH(A1&B1,Sheet2!\$C\$1:\$C\$6847, 0)))
</pre>

in cell C1 on Sheet1 and copy it down to C2133, it should give you what you want. You can then hide column C on Sheet2 if you want to.

3. ## 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)).

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

<img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Thank you very much. Both your solution and Legare's work quite well.

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.

Thanks again,
Deb

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

Since you are on Excel 2003, you could use the List feature...

Also a shorter formula whose result can be summed to obtain a total count:

In C2 (A-Cities) enter & copy down:

=A2&","&B2

This formula will be automatically adjusted by Excel when any addition or deletion to the list occurs.

In C2 (Master List) enter & copy down:

=--ISNUMBER(MATCH(A2&","&B2,'A-Cities'!\$C\$2:\$C\$98,0))

This formula will also be updated automatically by Excel itself when changes occur in A-Cities or in Master List.

See the attachment.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•