cell function

  • Thread starter Thread starter John Whitehead
  • Start date Start date
J

John Whitehead

How can I make a column name the state if the previous
column has the city in it, and then I would like the next
column to show the ZIP code with out me having to input
the info for each entry.

Thanks,
John Whitehead
 
If you have a 3-column table that associates cities with states and ZIP
codes, you can invoke a VLOOKUP formula...

B1:

=VLOOKUP(A1,Table,2,0)

C1:

VLOOKUP(A1,Table,3,0)
 
How can I make a column name the state if the previous
column has the city in it, and then I would like the next
column to show the ZIP code with out me having to input
the info for each entry.
Click to expand...

VLOOKUP would be the likely function, but for it to be useful you'd already need
to have city, state and zip code in a table somewhere else. Also, entering zip
code and having that populate city and state is usual way to do this since zip
codes are finer grained than either city or state (while there may be many zip
codes for cities and states, there's usually only one city and state for a given
zip code, though I live in a zip code that crosses city lines).
 
I don't live in the US but I wonder if such a DB would fit
on 1 worksheet...?

?)

Cheers
Juan
 
I don't live in the US but I wonder if such a DB would fit
on 1 worksheet...?
Click to expand...
...

My points (possibly not explicit enough) was that (1) what the OP wants to do
can only be done if such a table *were* accessible via cell formulas. It
wouldn't necessarily need to be exhaustive, but Excel on its own has not a clue
at all to which city or state zip code 50505 might apply. And (2) entering city,
e.g., Springfield, is ambiguous: there are cities named Springfield in most US
states (Delaware is the only state in the eastern time zone without one), so
entering Springfield would result in a list of 30-40 possible states, and since
some of these cities aren't small, there could be hundreds of zip codes from
which to choose.
 
I'm not nearly smart enough to understand anything on
McRitchie's URL and the "Help" on the "Help" didn't help
me any either. I need someone to tell me How to make
Column "F" put in Zip code 37186, and Column E put in TN
when I enter Bethpage in Column D and then repeat it if I
enter "Bethpage" in other lower rows
 
-----Original Message-----
...

VLOOKUP would be the likely function, but for it to be useful you'd already need
to have city, state and zip code in a table somewhere else. Also, entering zip
code and having that populate city and state is usual way to do this since zip
codes are finer grained than either city or state (while there may be many zip
codes for cities and states, there's usually only one city and state for a given
zip code, though I live in a zip code that crosses city lines).
Click to expand...
So how do I make it happen? If I put in the ZIP in
Column F how Do I make Column D show the city and Column
E show the state?
 
I'm not nearly smart enough to understand anything on
McRitchie's URL and the "Help" on the "Help" didn't help
me any either. I need someone to tell me How to make
Column "F" put in Zip code 37186, and Column E put in TN
when I enter Bethpage in Column D and then repeat it if I
enter "Bethpage" in other lower rows
Click to expand...

As you describe it, IT CAN'T BE DONE for the FIRST instance in which you enter
Bethpage as city. *YOU* or some other lucky human would need to enter TN as
state and 37186 as zip code the first time. The *ONLY* way around this is if you
*ALREADY* had a table of city, state and zip code somewhere else which Excel
could use to fill in the other columns. On its own, Excel HAS ABSOLUTELY NO IDEA
which cities are in which states and what their zip codes are. THERE'S NO WAY
AROUND THIS. Bitch, whine & moan all you want, no one's going to give you a
different answer.

However, for subsequent instances you could use formulas that look at previous
entries in the table. So, if the first entries were in D2 (city), E2 (state) and
F2 (zip code), you could use formulas in column E and F to fill in state and zip
code when the corresponding city is entered if it already appears above.

E3:
=IF(AND(COUNT($D3),COUNTIF($D$2:$D2,$D3)),VLOOKUP($D3,$D$2:$F2,2,0),"")

F3:
=IF(AND(COUNT($D3),COUNTIF($D$2:$D2,$D3)),VLOOKUP($D3,$D$2:$F2,3,0),"")

Select E3:F3 and fill them down as far as you think you need to. Then after you
enter Bethpage in D2, TN in E2 and 37186 in F2, when you enter Bethpage in
column D in subsequent rows, columns E and F will display TN and 37186,
respectively.

Caveats: If you have multiple zip codes for a given city, only the topmost one
in column F will display. Also, if you have the same city name in multiple
states, e.g., Springfield, these formulas will pull the state for the topmost
entry.
 
I quite understand what the OP want's and agree with you,
this is not possible *unless* he already has the database
with all the zips, citys and states... I was just curios
since Mexico has 31 states, and wild guessing probably 20
citys per stage more or less and probably 20 zip codes per
city more or less thats :12400 zip codes. and since the US
is more or less (probably more) 5 times the size of Mexico
just doing a straight calculation thats 62000 zip codes...
prety close to the XL limit of 65536...

I'm not arguing just general culture... :)
 
Back
Top