VLOOKUP formula?


C

colescand

I need help with a formula, I think I need to use VLOOKUP.

I have a group of zipcodes of specific locations in column A, and a blank column B for the congressional districts of those zip codes. In column C I have all possible zipcodes, and in column D I have the appropriate congressional district number matched up with each zip code in column C. (I.e., if Ihave zip code 11111 in column C, and that area code is in district 1, thanI have the number 1 in column D). I need excel to first match zipcodes from column A and C, and when they match, see which district number is attached to the column C zip code, and paste that into column B.

VLOOKUP formula? Or am I on the wrong track here?
 
Ad

Advertisements

C

Claus Busch

Hi,

Am Fri, 18 Oct 2013 12:24:21 -0700 (PDT) schrieb (e-mail address removed):
I have a group of zipcodes of specific locations in column A, and a blank column B for the congressional districts of those zip codes. In column C I have all possible zipcodes, and in column D I have the appropriate congressional district number matched up with each zip code in column C. (I.e., if I have zip code 11111 in column C, and that area code is in district 1, than I have the number 1 in column D). I need excel to first match zipcodes from column A and C, and when they match, see which district number is attached to the column C zip code, and paste that into column B.

try in B1:
=VLOOKUP(A1,$C$1:$D$1000,2,0)
modify the range C1:D1000 to suit and copy down


Regards
Claus B.
 
C

colescand

Hi,



Am Fri, 18 Oct 2013 12:24:21 -0700 (PDT) schrieb (e-mail address removed):






try in B1:

=VLOOKUP(A1,$C$1:$D$1000,2,0)

modify the range C1:D1000 to suit and copy down





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Claus-

Thanks for the reply- I'm a bit over my head here, my excel formula skills are pretty basic- was your suggestion to post that exact formula into B1 and then change the D$1000 to a different number to accommodate my range?

If so, I'm getting a "circular reference warning" and not information.
 
C

Claus Busch

Hi again,

Am Fri, 18 Oct 2013 12:57:34 -0700 (PDT) schrieb (e-mail address removed):
Actually, I fixed something, but now it's just giving me #N/A

in which range are the zip codes and the districts?
You will compare zip code from A with zip code from C and return
district from D?
The formula is correct. You only have to change the rows for D if your
table has less or more rows than 1000.
And if you get #N/A the zip code doesn't exist or the spelling in A and
C is different.



Regards
Claus B.
 
C

colescand

Hi again,



Am Fri, 18 Oct 2013 12:57:34 -0700 (PDT) schrieb (e-mail address removed):






in which range are the zip codes and the districts?

You will compare zip code from A with zip code from C and return

district from D?

The formula is correct. You only have to change the rows for D if your

table has less or more rows than 1000.

And if you get #N/A the zip code doesn't exist or the spelling in A and

C is different.







Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

The zip code range is from 98004-98597. There are 10 congressional districts (listed numbers,not words), and there are 21,328 location specific zips in A they need to be matched to. Not sure what the issue is, I'll try explanining it again to see if I said something wrong.

Column A- A unsorted list of 21, 328 zipcodes, that represent certain business locations.

Column B- blank, where hopefully the congressional district that corresponds to these codes will go

Column C- All zip codes for the region, in order from 98004 to 98597.

Column D- The congressional district for each one of the column C zip codes.. For example, 98004 is the first zip code in the area I'm looking at, and is thus C1. In D1 is "1" which is its congressional district. I want excel to then look at column A, see a 98004 area code, and put a one in its respective B column.
 
Ad

Advertisements

C

Claus Busch

Hi,

Am Fri, 18 Oct 2013 13:13:19 -0700 (PDT) schrieb (e-mail address removed):
The zip code range is from 98004-98597. There are 10 congressional districts (listed numbers,not words), and there are 21,328 location specific zips in A they need to be matched to. Not sure what the issue is, I'll try explanining it again to see if I said something wrong.
Column A- A unsorted list of 21, 328 zipcodes, that represent certain business locations.
Column B- blank, where hopefully the congressional district that corresponds to these codes will go
Column C- All zip codes for the region, in order from 98004 to 98597.
Column D- The congressional district for each one of the column C zip codes. For example, 98004 is the first zip code in the area I'm looking at, and is thus C1. In D1 is "1" which is its congressional district. I want excel to then look at column A, see a 98004 area code, and put a one in its respective B column.

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "ZipCodes"


Regards
Claus B.
 
C

Claus Busch

Hi Albert,

Am Fri, 18 Oct 2013 19:43:54 -0400 schrieb Albert:
How do I connect to your SKYDRIVE ?

click the link. In SkyDrive you can open the workbook into the WebApp or
you rightclick on the workbook and download it.


Regards
Claus B.
 
A

Albert

Good morning
Claus Busch said:
Hi Albert,

Am Fri, 18 Oct 2013 19:43:54 -0400 schrieb Albert:


click the link. In SkyDrive you can open the workbook into the WebApp or
you rightclick on the workbook and download it.
Regards
Claus B.

It open the SKYDRIVE page and prompt me to enter my Microsoft account ID
and PWD, then it opens my OUTLOOK mail and Skydrive.

I must be missing something.
 
C

Claus Busch

Hi Albert,

Am Sat, 19 Oct 2013 08:12:29 -0400 schrieb Albert:
It open the SKYDRIVE page and prompt me to enter my Microsoft account ID
and PWD, then it opens my OUTLOOK mail and Skydrive.

you see my files and you can open them with click in SkyDrive or you can
download them with rightclick. You don't need to connect with your PW.


Regards
Claus B.
 
Ad

Advertisements

A

Albert

HI
Claus Busch said:
Hi Albert,

Am Sat, 19 Oct 2013 08:12:29 -0400 schrieb Albert:


you see my files and you can open them with click in SkyDrive or you can
download them with rightclick. You don't need to connect with your PW.

To open the link I sent it to my OUTLOOK mail account, and opened the
message and click the link.
Your whole PUBLIC files became accessible.

I opened download the VLookup file and surprise, your file formulas are
translated to french

=IFERROR(VLOOKUP(A1,$C$1:$D$1000,2,0),"")

=SIERREUR(RECHERCHEV(A1;$C$1:$D$1000;2;0);"")

thanks
 
Ad

Advertisements

C

Claus Busch

Hi Albert,

Am Sat, 19 Oct 2013 08:50:05 -0400 schrieb Albert:
I opened download the VLookup file and surprise, your file formulas are
translated to french

the built in functions will be translated to the language version the
opening Excel is.
I create all examples in German. But everyone can open them in his own
language.


Regards
Claus B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top