Populating a field using a pull-down/combo box menu

[Brablo]

Hello all,

I'm working on MS Excel. I am trying to find the source code for
something that will allow me to do the following novel procedure with a
pull-down menu and a text box: There is a pull-down menu, and another
text box right besides the pull-down menu. The pull-down menu has the
names of all the states in the USA in it. When one state is selected,
the text box is automatically populated with the capital of that state.


In MS Excel, I've created a novel way to handle this issue using
Boolean logic. Unfortunately, I can only have 7 states in my pull-down
menu, and the source code is very unwieldy:

IF(Sheet2!D1=1,Sheet2!A1, IF(Sheet2!$D$1=2,Sheet2!A2,
IF(Sheet2!$D$1=3,Sheet2!A3, IF(Sheet2!$D$1=4,Sheet2!A4,
IF(Sheet2!$D$1=5,Sheet2!A5, IF(Sheet2!$D$1=6,Sheet2!A6,
IF(Sheet2!$D$1=7,Sheet2!A7, IF(Sheet2!$D$1=8,Sheet2!A8))))))))

 

[Barb Reinhardt]

Look at the VLOOKUP function and see if that gets you what you want.

 

[Brablo]

Thanks for your guidance. I think I need some guidance with this
function.

Suppose that I have a column of major cities in the USA in column A.
Column B tells me which state that major city is located in. Some of
the data is:

MajCity State
San Jose CA
San Francisco CA
Detroit MI
Boston MA
Albany NY
New York NY

I have a pulldown menu that has a list of all the "MajCity". When the
user selects one of the cities, Albany for example, the output is "NY",
and this is to be displayed in E55.

What's the algorithm for this problem?

 

[L. Howard Kittle]

Hi Brablo,

Per Barb's suggestion of VLOOKUP:

=VLOOKUP(C1,J1:K6,2,0)

Where your dropdown list of cities is in C1
Where your table of cities and states are in J1:K6
The Vlookup formula is in F1

Select a city in C1 and the state is returned in F1.

HTH
Regards,
Howard