Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 5
Talking How to use matching values in two worksheets to return another value

Hello all. My first post. I have two worksheets A and B. Both contain columns of five digit zip code values. A is of names and address (street/city/state/zip different columns) with 3500 rows. B is a sheet with every zip, city, state and county in the US. B is sorted by zip.

For each contact in A I want to lookup the same zip in B and copy(merge?) the appropriate (same row) county into A.

"A" example
Barnaby Joyce 2 Joyce Plaza Stony Point NY 10980
Frank Polloni 614 C Way W Stockbridge MA 01266
John Wall 2 Strawtown Road West Nyack NY 10994

"B" example
10980 STONY POINT NY ROCKLAND
00544 W STOCKBRIDGE MA BERKSHIRE
10994 WEST NYACK NY ROCKLAND

Do the sheets need to be sorted ascending by zip to do the lookup?

Thank you
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 829
Default How to use matching values in two worksheets to return another value

"ricklongbow" <rick at weinsteinz dot com wrote:
I have two worksheets A and B. Both contain
columns of five digit zip code values. A is of names and address
(street/city/state/zip different columns) with 3500 rows. B is a sheet
with every zip, city, state and county in the US. B is sorted by zip.
For each contact in A I want to lookup the same zip in B and
copy(merge?) the appropriate (same row) county into A.

[....]
Do the sheets need to be sorted ascending by zip to do the lookup?


That depends on which lookup function that you use. In any case, it would
only be necessary that the lookup column in sheet B be in ascending order by
zip code. And the lookup is __many_times__ more efficient if it is. But you
said it already is. Good choice!

To be clear, the data in sheet A can be in any order.

You were not clear exactly where you want to put the looked-up county into
sheet A. You can insert a column before the zip code column, or you can use
any column to the right.

Suppose your data in sheet B are in A2:D100000, and your data in sheet A are
in columns A:D starting in row 2. Put the following formula into E2 and
copy down:

=VLOOKUP(D2,'sheet B'!$A$2:$D$100000,4)

  #3   Report Post  
Junior Member
 
Posts: 5
Default

Quote:
Originally Posted by joeu2004[_2_] View Post
"ricklongbow" <rick at weinsteinz dot com wrote:
I have two worksheets A and B. Both contain
columns of five digit zip code values. A is of names and address
(street/city/state/zip different columns) with 3500 rows. B is a sheet
with every zip, city, state and county in the US. B is sorted by zip.
For each contact in A I want to lookup the same zip in B and
copy(merge?) the appropriate (same row) county into A.

[....]
Do the sheets need to be sorted ascending by zip to do the lookup?


That depends on which lookup function that you use. In any case, it would
only be necessary that the lookup column in sheet B be in ascending order by
zip code. And the lookup is __many_times__ more efficient if it is. But you
said it already is. Good choice!

To be clear, the data in sheet A can be in any order.

You were not clear exactly where you want to put the looked-up county into
sheet A. You can insert a column before the zip code column, or you can use
any column to the right.

Suppose your data in sheet B are in A2:D100000, and your data in sheet A are
in columns A:D starting in row 2. Put the following formula into E2 and
copy down:

=VLOOKUP(D2,'sheet B'!$A$2:$D$100000,4)
Thank you very much. I'm not sure I understand the syntax as I'm a bit new at this. With these actual parameters what would the formula be?

Sheet B (named ZIP_CODES) - 5 digit zips are in cells A2:42742. Ascending sort. Counties are D2:42742

Sheet A (named emergency contacts) 5 digit zips are in N2:2709. Ascending sort. I want to return couties into Q2:2709.

Sheet A actually extends out to CP. Where would I insert the formula? And how do I execute it once copied in?

Pardon my needs for beginners explaination but that is what I am ! Thank you very much.

Last edited by ricklongbow : January 10th 13 at 06:31 AM
  #4   Report Post  
Junior Member
 
Posts: 5
Default

Quote:
Originally Posted by ricklongbow View Post
Thank you very much. I'm not sure I understand the syntax as I'm a bit new at this. With these actual parameters what would the formula be?

Sheet B (named ZIP_CODES) - 5 digit zips are in cells A2:42742. Ascending sort. Counties are D2:42742

Sheet A (named emergency contacts) 5 digit zips are in N2:2709. Ascending sort. I want to return couties into Q2:2709.

Sheet A actually extends out to CP. Where would I insert the formula? And how do I execute it once copied in?

Pardon my needs for beginners explaination but that is what I am ! Thank you very much.

I am afraid I was not sufficiently precise asking the initial question. Let me restate it. My apologies

I have two single sheet workbooks (seperate files) "contactsexport12282012" and "ZIP_CODES" in Outlook 2010. Both contain columns of five digit zip code values (contactsexport12282012 N2-2675 and ZIP_CODES A2-42742). ZIP_CODES also contains the coordinating county names in F2-42742.

For each contact (row) in contactsexport12282012 I want to lookup the same zip in ZIP_CODES and import the county name into contactsexport12282012 P2-2675 (empty column).

The full filenames a C:\Users\Rick\Desktop\ZIP_CODES C:\DATA\contactsexport12282012.

NOTE - Would it be helpful if I temporarily copy the ZIP_CODES data onto the contactsexport12282012 sheet??

I'm not trying to learn how (right now) as I am short on time. I also need instruction on where to put it and how to execute it. Like I said, I'm a newbie. Thank you very much.
  #5   Report Post  
Junior Member
 
Posts: 5
Default

Quote:
Originally Posted by ricklongbow View Post
Thank you very much. I'm not sure I understand the syntax as I'm a bit new at this. With these actual parameters what would the formula be?

Sheet B (named ZIP_CODES) - 5 digit zips are in cells A2:42742. Ascending sort. Counties are D2:42742

Sheet A (named emergency contacts) 5 digit zips are in N2:2709. Ascending sort. I want to return couties into Q2:2709.

Sheet A actually extends out to CP. Where would I insert the formula? And how do I execute it once copied in?

Pardon my needs for beginners explaination but that is what I am ! Thank you very much.

I am afraid I was not sufficiently precise asking the initial question. Let me restate it. My apologies

I have two single sheet workbooks (seperate files) "contactsexport12282012" and "ZIP_CODES" in Outlook 2010. Both contain columns of five digit zip code values (contactsexport12282012 N2-2675 and ZIP_CODES A2-42742). ZIP_CODES also contains the coordinating county names in F2-42742.

For each contact (row) in contactsexport12282012 I want to lookup the same zip in ZIP_CODES and import the county name into contactsexport12282012 P2-2675 (empty column).

The full filenames a C:\Users\Rick\Desktop\ZIP_CODES C:\DATA\contactsexport12282012.

NOTE - Would it be helpful if I temporarily copy the ZIP_CODES data onto the contactsexport12282012 sheet??

I'm not trying to learn how (right now) as I am short on time. I also need instruction on where to put it and how to execute it. Like I said, I'm a newbie. Thank you very much.


  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 829
Default How to use matching values in two worksheets to return another value

"ricklongbow" <rick at weinsteinz dot com wrote:
I'm not trying to learn how (right now) as I am short on time. I also
need instruction on where to put it and how to execute it. Like I said,
I'm a newbie. Thank you very much.


My apologies! I had lost track of this discussion, and it is now nearly a
month later.

Do you still need assistance?

If so, should I just continue from where we left off (your last to
postings)? Or have you made some progress, and you want to "update" your
request with more current information?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use matching values in two worksheets to return another value ricklongbow New Users to Excel 1 January 20th 13 03:18 PM
compare data from different worksheets and return matching results spmu Excel Discussion (Misc queries) 1 July 11th 09 06:11 AM
search worksheets in same book to return value matching criteria? Jane Doe[_3_] Excel Worksheet Functions 3 September 10th 08 10:43 PM
matching two worksheets and return to different value dan Excel Worksheet Functions 2 October 23rd 06 11:18 PM
Return all matching values [email protected] Excel Worksheet Functions 4 August 9th 06 04:02 AM


All times are GMT +1. The time now is 09:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"