How to use matching values in two worksheets to return another value

R

ricklongbow

Hello all. My first post. I have two worksheets A and B. Both contai
columns of five digit zip code values. A is of names and addres
(street/city/state/zip different columns) with 3500 rows. B is a shee
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 an
copy(merge?) the appropriate (same row) county into A.

"A"
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"
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 yo
 
J

joeu2004

ricklongbow said:
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)
 
R

ricklongbow

'joeu2004[_2_ said:
;1608521']"ricklongbow said:
I have two worksheets A and B. Both contai
columns of five digit zip code values. A is of names and addres
(street/city/state/zip different columns) with 3500 rows. B is a shee
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 an
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, i
would
only be necessary that the lookup column in sheet B be in ascendin
order by
zip code. And the lookup is __many_times__ more efficient if it is. Bu
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 count
into
sheet A. You can insert a column before the zip code column, or you ca
use
any column to the right

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

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 bi
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:4274

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 ! Than
you very much
 
R

ricklongbow

ricklongbow;1608525 said:
Thank you very much. I'm not sure I understand the syntax as I'm a bi
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 ! Than
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 contai
columns of five digit zip code values (contactsexport12282012 N2-267
and ZIP_CODES A2-42742). ZIP_CODES also contains the coordinating count
names in F2-42742.

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

The full filenames are: C:\Users\Rick\Desktop\ZIP_CODE
C:\DATA\contactsexport12282012.

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

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

ToniLM

ricklongbow;1608511 said:
Hello all. My first post. I have two worksheets A and B. Both contai
columns of five digit zip code values. A is of names and addres
(street/city/state/zip different columns) with 3500 rows. B is a shee
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 an
copy(merge?) the appropriate (same row) county into A.

"A"
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"
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

Have you tried using a combination of concatenate, left and right
 
J

joeu2004

ricklongbow said:
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?
 
Top