Cell to return value depending on item chosen from dropdown list

[MAGGIE_12]

Good Day, I am a first time poster, and I hope i am doing this right

I have an excel workbook which contains a separate tab with all the drop-down lists, which is used in the all the other tabs.

My Brain Boggle: I have City, Province in separate columns (in the drop-down list tab), So if I choose a City from the drop-down list, I want excel to automatically fill the Province in the cell next to it.

Can this be done? Any assistance will be appreciated so much. Thank you.

Maggie_12

 

[etaf]

build a table of all the cities and province

say new sheet
column a = city
column b = province

now you can use a vlookup()
to lookup the province

 

[MAGGIE_12]

Hi Etaf,

I have done the table and used the VLOOKUP function: =VLOOKUP(H5;Table5[Province];2;1)

But it keeps giving me a #N/A Error.

What am I doing wrong here?

Thanks a million.

 

[etaf]

lets assume its in sheet1 range A1:B100

the vlookup would be

=vlookup(H5, Sheet1!$A$1:$B$100, 2, false)

i think a 1 at the end is TRUE - so nearest value
also not sure if

Table5[Province]

is actually the right name for the range - TRY using sheet and range with cells rather than a table
and 0 for false
=VLOOKUP(H5;RANGE;2;false)

also check that you dont have spaces in the dropdown and in the table

"london "
will return a n/a
if the table has
"london"

and not
"london "

 

[MAGGIE_12]

I have done the following and it tells me that there is a problem with the formula:

=VLOOKUP(H5,Drop_Lists!$f$2:$f$75,2,false)

I have also checked for spaces

Any further ideas?

 

[MAGGIE_12]

=VLOOKUP(H5,Drop_Lists!$E$2:$F$75,2,false)

Sorry pasted the wrong one before.

 

[etaf]

i guess i need to see a copy
can you put on dropbox or onedrive and share

 

[MAGGIE_12]

Hi There, I don't have a dropbox account, can I email it to you (it is really small) 1.17mb

 

[MAGGIE_12]

I got it right:

=VLOOKUP(H5;Table3;2;1)