state abbreviations

[gls858]

Does anyone have a way of converting state abbreviations to the full
state name? Example AZ to Arizona. I have two lists I need to sort by
state and compare. One list has the abbreviation and the other the full
name. Of course they don't sort the same.

gls858

 

[Mike H]

There's a noce sorted list here

http://www.usps.com/ncsc/lookups/usps_abbreviations.html

They're the wrong way round for what you want but that's simply a matter of
manually reversing the columns. Then

=VLOOKUP("AZ",A1:B59,2,FALSE)

AZ could of coyrse be a cell reference

Mike

 

[gls858]

There's a noce sorted list here

http://www.usps.com/ncsc/lookups/usps_abbreviations.html

They're the wrong way round for what you want but that's simply a matter of
manually reversing the columns. Then

=VLOOKUP("AZ",A1:B59,2,FALSE)

AZ could of coyrse be a cell reference

Mike

Thanks Mike. I'll have to give it a try. Just got something else thrown
on my desk so it may be a while before I get back to the lists. I'll
try and post back to let you know if I got it to work.

gls858

 

[gls858]

There's a noce sorted list here

http://www.usps.com/ncsc/lookups/usps_abbreviations.html

They're the wrong way round for what you want but that's simply a matter of
manually reversing the columns. Then

=VLOOKUP("AZ",A1:B59,2,FALSE)

AZ could of coyrse be a cell reference

Mike

Thanks for the help Mike I finally got around to looking at this
and I can't seem to get VLOOKUP to do what I want. I'll try a more
detailed explanation of what I would like to do. Maybe that will help.
I have a list of names with address and the state is represented by
it's two letter abbreviation with multiple entries for each state
(over 500) like so:
AZ
AZ
AZ
AR
AR
AR
CA
CA


I would like to add another column with a formula that would change the
abbreviation to the full name like so:
Arizona
Arizona
Arizona
Arkansas
Arkansas
Arkansas
California
California

Is this possible with Vlookup?

gls858

 

[Chip Pearson]

Assuming the following:

Abbreviations in K1:K50
Full Names in L1:L50

Your state abbreviations in A1:A50

use

=VLOOKUP(A1,$K$1:$L$50,2,FALSE)

to return the full name of the state whose abbreviation is in your data in
cell A1.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[gls858]

Assuming the following:

Abbreviations in K1:K50
Full Names in L1:L50

Your state abbreviations in A1:A50

use

=VLOOKUP(A1,$K$1:$L$50,2,FALSE)

to return the full name of the state whose abbreviation is in your data
in cell A1.

Thanks Chip! Worked like a charm. I'm assuming the absolute value causes
the lookup to start at the top of the coll each time.

gls858

 

[Chip Pearson]

I'm assuming the absolute value causes
the lookup to start at the top of the coll each time.

Yes. Since the lookup range has absolute references, it will not change as
you copy/fill the formula down a column. The lookup value has a relative
range so that it will change as you fill down.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[gls858]

Yes. Since the lookup range has absolute references, it will not change
as you copy/fill the formula down a column. The lookup value has a
relative range so that it will change as you fill down.

Thanks for the explanation.

gls858