In Column A I have a list of states by their abbreviations (e.g. AL, NC, SC,
TN, WV, FL, MS, etc) and I want to be able to refine this list into 3
different territories (e.g. North, South, East). For example AL and FL are in
the South, NC, SC, WV are in the North and TN and MS are in the South.
I am trying to create a formula that when I highlight A1 (which says FL) B1
will return "South".
Any help would be appreciated
Thanks

Hi,
Take a look to Debra Web

http://www.contextures.com/xlDataVal02.html#TwoWord

If this was helpful please say yes. Thank you

"phd4212" wrote:

In Column A I have a list of states by their abbreviations (e.g. AL, NC, SC,
TN, WV, FL, MS, etc) and I want to be able to refine this list into 3
different territories (e.g. North, South, East). For example AL and FL are in
the South, NC, SC, WV are in the North and TN and MS are in the South.
I am trying to create a formula that when I highlight A1 (which says FL) B1
will return "South".
Any help would be appreciated
Thanks

This requires the use of LOOKUP.
Create a seperate reference table (in the same worksheet is easiest), with a
state column and a territory column.
Then put a lookup formula in the cell where you want the result.
The LOOKUP formula will need a "lookup_value" -- the state in the main table
a "lookup_vector" -- the state
in the ref table
and a "result_vector" -- the terr in
the ref table


"phd4212" wrote:

In Column A I have a list of states by their abbreviations (e.g. AL, NC, SC,
TN, WV, FL, MS, etc) and I want to be able to refine this list into 3
different territories (e.g. North, South, East). For example AL and FL are in
the South, NC, SC, WV are in the North and TN and MS are in the South.
I am trying to create a formula that when I highlight A1 (which says FL) B1
will return "South".
Any help would be appreciated
Thanks

Define three Named Ranges (North, South, and East) with each state in one of
the 3 Named Ranges. Make three lists of the state abbreviations and then use
menu below to define these ranges

menu Insert - Names - Defined


then use a formula like this to get your results. A1 contains the
abbreviation you want to look up

=IF(COUNTIF(North,A1)0,"North",IF(COUNTIF(South,A 1)0,"South","East"))


"phd4212" wrote:

In Column A I have a list of states by their abbreviations (e.g. AL, NC, SC,
TN, WV, FL, MS, etc) and I want to be able to refine this list into 3
different territories (e.g. North, South, East). For example AL and FL are in
the South, NC, SC, WV are in the North and TN and MS are in the South.
I am trying to create a formula that when I highlight A1 (which says FL) B1
will return "South".
Any help would be appreciated
Thanks

On Feb 23, 7:54*pm, phd4212 wrote:
In Column A I have a list of states by their abbreviations (e.g. AL, NC, SC,
TN, WV, FL, MS, etc) and I want to be able to refine this list into 3
different territories (e.g. North, South, East). For example AL and FL are in
the South, NC, SC, WV are in the North and TN and MS are in the South.
I am trying to create a formula that when I highlight A1 (which says FL) B1
will return "South".
Any help would be appreciated
Thanks

First you have to construct your criteria table, which says which
state belong to which category. Then you you can use a lookup function
to pool out the criteria from that table.

I think this is the best way for me to go, but when I try to apply the named
range it always says that it cannot find any references. Is this because I do
not have a formula in this reference. Since I cannot apply the array, an
error returns when I apply this formula. Thanks for the help thusfar.

"Joel" wrote:

Define three Named Ranges (North, South, and East) with each state in one of
the 3 Named Ranges. Make three lists of the state abbreviations and then use
menu below to define these ranges

menu Insert - Names - Defined


then use a formula like this to get your results. A1 contains the
abbreviation you want to look up

=IF(COUNTIF(North,A1)0,"North",IF(COUNTIF(South,A 1)0,"South","East"))


"phd4212" wrote:

In Column A I have a list of states by their abbreviations (e.g. AL, NC, SC,
TN, WV, FL, MS, etc) and I want to be able to refine this list into 3
different territories (e.g. North, South, East). For example AL and FL are in
the South, NC, SC, WV are in the North and TN and MS are in the South.
I am trying to create a formula that when I highlight A1 (which says FL) B1
will return "South".
Any help would be appreciated
Thanks