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.

I can think of a couple ways to do this. One is with a nested IF statement.
Assuming the cell you're interrogating is A2, you could use:

=if(or(A2="Tri-Cities", A2="Tidewater", A2="Roanoke",
A2="Richmond/Tidewater", A2="Richmond"),"CLR", if(A2="Knoxville","CLK",
if(A2="Charlotte","CLC","ERROR")))

Another way would be to set up a table with the city names and one column
and the appropriate CLR, CLK, or CLC designation in a second column. You
could then use VLOOKUP to find the correct desitnation.

Hope this helps.
Will

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

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.

Thanks for the help. I see the formula refers to A2, how would this look if
I wanted to refer to the entire column for the lookup?



"roadkill" wrote:

I can think of a couple ways to do this. One is with a nested IF statement.
Assuming the cell you're interrogating is A2, you could use:

=if(or(A2="Tri-Cities", A2="Tidewater", A2="Roanoke",
A2="Richmond/Tidewater", A2="Richmond"),"CLR", if(A2="Knoxville","CLK",
if(A2="Charlotte","CLC","ERROR")))

Another way would be to set up a table with the city names and one column
and the appropriate CLR, CLK, or CLC designation in a second column. You
could then use VLOOKUP to find the correct desitnation.

Hope this helps.
Will

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

With this method you need to refer to one cell at a time. Write the formula
in one cell (e.g. B2, referencing A2) and then copy the formula to the rest
of the cells in the column. Excel will automatically adjust the referenced
cell (e.g. A3, A4, A5) going down the column.
Will

"Jim" wrote:

Thanks for the help. I see the formula refers to A2, how would this look if
I wanted to refer to the entire column for the lookup?



"roadkill" wrote:

I can think of a couple ways to do this. One is with a nested IF statement.
Assuming the cell you're interrogating is A2, you could use:

=if(or(A2="Tri-Cities", A2="Tidewater", A2="Roanoke",
A2="Richmond/Tidewater", A2="Richmond"),"CLR", if(A2="Knoxville","CLK",
if(A2="Charlotte","CLC","ERROR")))

Another way would be to set up a table with the city names and one column
and the appropriate CLR, CLK, or CLC designation in a second column. You
could then use VLOOKUP to find the correct desitnation.

Hope this helps.
Will

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

hi Jim,

assumption : your data starts on A1
in B1 type
=IF(A1="","",IF(A1="Charlotte","CLC",IF(A1="Knoxvi lle","CLK","CLR")))
and copy down the fomula as per your need,

click yes below, if it works for you

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