The best way to approach this is to setup a list on another sheet with the
cities in column A and their respective codes in column B. Then go to Insert
Names Define Name, call your new list something meaningful (like
CityCodes), and enter the following formula for the source:
=offset(Sheet2!$A$1,0,0,counta(Sheeet2!$A:$A),2)

Back to the sheet you're working in now, you'll just use this formula to
retrieve the code (assuming you start with B2):
=vlookup(A2,CityCodes,2,0)

If the city does not exist in your list, you'll get an error (#N/A!),
telling you to get your list updated. You can add new cities and codes any
time on Sheet2.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Jim" wrote:

Hello,

I have two columns. Column A has city names which include:
Tri-Cities
Tidewater
Roanoke
Richmond/Tidewater
Richmond
Knoxville
Charlotte

I need help writing a formula that will return CLR, CLK or CLC in Column B.
Here is the break down:

CLR
if Tri-Cities, Tidewater, Roanoke, Richmond/Tidewater or Richmond

CLK
if Knoxville

CLC
if Charlotte

Thanks so much for the help.