Is this possible? If so what function do I use?
 

My data is:
A B C
TX United States
MA
NY United States
NJ
NH United States
London United Kingdom
Paris France


Column A has states that are almost always populated, but B is often left
blank. I wanted to create some type of Array or Lookup that has all the
states in it, and them do a comparison; If what is in Column A is a valid
state, then populate B with "United States, otherwise leave it alone.

I can't use "IF" because there are too many arguments, and can't figure out
how to make it work with something like VLOOKUP.

Advice apreciated.
~S

Is this possible? If so what function do I use?
 

Put a list of valid states somewhere, in this case H1 - H50 then try this
formula:-


=IF(COUNTIF($H$1:$H$50,A1)0,"United States","")

drag down as required

Mike

This looks

"AdirondackSam" wrote:

My data is:
A B C
TX United States
MA
NY United States
NJ
NH United States
London United Kingdom
Paris France


Column A has states that are almost always populated, but B is often left
blank. I wanted to create some type of Array or Lookup that has all the
states in it, and them do a comparison; If what is in Column A is a valid
state, then populate B with "United States, otherwise leave it alone.

I can't use "IF" because there are too many arguments, and can't figure out
how to make it work with something like VLOOKUP.

Advice apreciated.
~S

Is this possible? If so what function do I use?
 

Create a 1 column table of all 50 states. Assume this table is in the range
D1:D50.

Then, in column B:

=IF(COUNTIF(D$1:D$50,A1),"United States","")

Copy down as needed.

--
Biff
Microsoft Excel MVP


"AdirondackSam" wrote in message
...
My data is:
A B C
TX United States
MA
NY United States
NJ
NH United States
London United Kingdom
Paris France


Column A has states that are almost always populated, but B is often left
blank. I wanted to create some type of Array or Lookup that has all the
states in it, and them do a comparison; If what is in Column A is a valid
state, then populate B with "United States, otherwise leave it alone.

I can't use "IF" because there are too many arguments, and can't figure
out
how to make it work with something like VLOOKUP.

Advice apreciated.
~S

Is this possible? If so what function do I use?
 

Hi

One way
Create a list of the States in say column A of Sheet2 then use
=IF(ISNUMBER(MATCH(A2,Sheet2!A:A)),"United States","Other")

--
Regards
Roger Govier



"AdirondackSam" wrote in message
...
My data is:
A B C
TX United States
MA
NY United States
NJ
NH United States
London United Kingdom
Paris France


Column A has states that are almost always populated, but B is often left
blank. I wanted to create some type of Array or Lookup that has all the
states in it, and them do a comparison; If what is in Column A is a valid
state, then populate B with "United States, otherwise leave it alone.

I can't use "IF" because there are too many arguments, and can't figure
out
how to make it work with something like VLOOKUP.

Advice apreciated.
~S

Is this possible? If so what function do I use?
 

Great list available he
http://www.usps.com/ncsc/lookups/abbr_state.txt

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"T. Valko" wrote in message
...
Create a 1 column table of all 50 states. Assume this table is in the
range D1:D50.

Then, in column B:

=IF(COUNTIF(D$1:D$50,A1),"United States","")

Copy down as needed.

--
Biff
Microsoft Excel MVP


"AdirondackSam" wrote in message
...
My data is:
A B C
TX United States
MA
NY United States
NJ
NH United States
London United Kingdom
Paris France


Column A has states that are almost always populated, but B is often left
blank. I wanted to create some type of Array or Lookup that has all the
states in it, and them do a comparison; If what is in Column A is a
valid
state, then populate B with "United States, otherwise leave it alone.

I can't use "IF" because there are too many arguments, and can't figure
out
how to make it work with something like VLOOKUP.

Advice apreciated.
~S

Is this possible? If so what function do I use?
 

Thanks all - suggestions were spot on and I was able to do what I wanted.