Three Conditions in one cell

[Jim]

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.

 

[roadkill]

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

 

[KC hotmail com>]

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.

 

[Jim]

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]

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

 

[YESHWANT]

hi Jim,

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

click yes below, if it works for you