Matching data on two spreadsheets

**TomFoolery** · 12-01-2010, 09:58 AM

Hello folks. My experience with Excel is pretty basic, so I've had a spot of trouble trying to figure out this one trick. I'd be much obliged if you could help.

I have one spreadsheet that has a list of people and their addresses (including zip code).
I have a 2nd spreadsheet that has a list of territories unique to my organization defined by zip code.

Is it possible to add a column to the first sheet that automatically lists what territory folks fall into, based on the information on the 2nd sheet?

Thanks in advance for any help you can lend me.

**NBVC** · 12-01-2010, 10:26 AM

Are the zipcodes in sheet 1 in separate column from the rest of the addresses?

Post a sample workbook of what you have (please change confidential information to protect the actual people)....

**TomFoolery** · 12-01-2010, 11:37 AM

Yes. In both cases the Zip Code is in its own column. I'm not sure how to post an example from Excel, but it would look something like this (sorry, I know this won't look pretty):

Name/Zip Code/Territory
Tom/10004/?
****/10004/?
Harry/10011/?

Territory/Zip Code
East NYC/10004
East NYC/10011
East NYC/10038
Tulsa, OK/74101

So each Territory is comprised of a slew of different Zip Codes. Some of those Zip Codes will be empty, and some will have a handful of folks living in them. Does that answer your question?

Edit: Apparently excelforum doesn't approve of one of the names in "sheet 1". Imagine his name is "Richard" instead.

**NBVC** · 12-01-2010, 11:45 AM

Something like:

=Index('Sheet2'!A:A,Match(B2,'Sheet2'!B:B,0))

where Sheet2 columns A and B hold your Territory/Zip lookup table

And B2 in the current sheet it first Zip to look up.

Then copy formula down.

**TomFoolery** · 12-01-2010, 01:12 PM

Okay, that might be a little over my head, but someone here in the office should be able to translate that for me. Thank you kindly, NBVC.

**NBVC** · 12-01-2010, 01:17 PM

You can also look here for more help:

http://www.contextures.com/xlfunctions03.html

basically, the Match() function looks for the value that is in C2 and tries to find it in column B in Sheet2, if it finds it, it returns the position with column B that the match is found in.

The INDEX() function indexes the range you want to extract from and returns the item at the same location as the postion number found by the Match function.

So if Match says there is a match at position 5 (row 5) then Index returns what is in column A in that same row...

**Lindrew** · 01-24-2012, 10:46 AM

Hi,

I am trying to complete a mailing list, and want to match data from two spreadsheets.

Sheet1 contains a list of all my contacts, and Sheet2 contains a list of some of these contacts, the ones I do not want to include in my mailing. I basically want to remove all the contacts on Sheet2 from Sheet1, how do I do this?

Grateful for any help.

**NBVC** · 01-24-2012, 11:05 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.