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.