A Method of Replacing

J

JohnnyCat

Hello,

I have two excel sheets. One sheet has a list of, for example, state
and the associated abbreivation for that state. On my other sheet
have a list of the states without abbreivations. I would like t
replace the full state name on one sheet with the abbreivation on th
other sheet.

If this is posssible, any help would be greatly appreciated. Thanks
 
B

Bob Phillips

On sheet 2, in a new column

=VLOOKUP(A2,Sheet1!A2:B100,2,false)

Select column B, copy, Edit>Pastespecial, click Values and OK
Delete the full name column

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Z

Zack Barresse

I would actually use ...

=VLOOKUP(A1,Sheet1!$A$2:$B$100,2,0)

... so you won't miss/add result vectors.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.
 
J

JohnnyCat

I am very happy there is a way to do this. Thanks for your help. I am
having probelms getting this forumla to work. I think I need some
clarification on how this formula will work.

On Sheet1 I have:

A1 = STATE NAME B1 = Abbreviation

On Sheet2 I have:

A1 = STATE NAME and I paste the formula into B1.

When trying the forumla on Sheet2 I get N/A.

I tried the first formula, but it gave the wrong abbreviations for the
states.

A question also - Does this formula assume that the STATE NAME in A1 on
Sheet1 is equal to the STATE NAME on Sheet2 in A1?

Thanks again.
 
Z

Zack Barresse

Yes, it will look for a match. Read the Help files on VLOOKUP. The final
portion of the syntax is in regards to finding an exact match or partial
match. 0 or FALSE is an exact match whereas 1 or TRUE is not an exact
match. If a result is not found in either circumstance #N/A is returned.

Let's say your list in Sheet 1 is all in caps, in column A, and your list of
state names on Sheet 2, column A, is not all caps. Then you could adjust
the formula as such...

=VLOOKUP(UPPER(A1),Sheet1!$A$1:$B$100,2,0)

HTH
 
Top